1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23
  24from sqlglot.errors import ParseError
  25from sqlglot.helper import (
  26    AutoName,
  27    camel_to_snake_case,
  28    ensure_collection,
  29    seq_get,
  30    split_num_words,
  31    subclasses,
  32)
  33from sqlglot.tokens import Token
  34
  35if t.TYPE_CHECKING:
  36    from sqlglot.dialects.dialect import DialectType
  37
  38
  39class _Expression(type):
  40    def __new__(cls, clsname, bases, attrs):
  41        klass = super().__new__(cls, clsname, bases, attrs)
  42
  43        # When an Expression class is created, its key is automatically set to be
  44        # the lowercase version of the class' name.
  45        klass.key = clsname.lower()
  46
  47        # This is so that docstrings are not inherited in pdoc
  48        klass.__doc__ = klass.__doc__ or ""
  49
  50        return klass
  51
  52
  53class Expression(metaclass=_Expression):
  54    """
  55    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  56    context, such as its child expressions, their names (arg keys), and whether a given child expression
  57    is optional or not.
  58
  59    Attributes:
  60        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  61            and representing expressions as strings.
  62        arg_types: determines what arguments (child nodes) are supported by an expression. It
  63            maps arg keys to booleans that indicate whether the corresponding args are optional.
  64
  65    Example:
  66        >>> class Foo(Expression):
  67        ...     arg_types = {"this": True, "expression": False}
  68
  69        The above definition informs us that Foo is an Expression that requires an argument called
  70        "this" and may also optionally receive an argument called "expression".
  71
  72    Args:
  73        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  74        parent: a reference to the parent expression (or None, in case of root expressions).
  75        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  76            uses to refer to it.
  77        comments: a list of comments that are associated with a given expression. This is used in
  78            order to preserve comments when transpiling SQL code.
  79        _type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  80            optimizer, in order to enable some transformations that require type information.
  81    """
  82
  83    key = "expression"
  84    arg_types = {"this": True}
  85    __slots__ = ("args", "parent", "arg_key", "comments", "_type")
  86
  87    def __init__(self, **args: t.Any):
  88        self.args: t.Dict[str, t.Any] = args
  89        self.parent: t.Optional[Expression] = None
  90        self.arg_key: t.Optional[str] = None
  91        self.comments: t.Optional[t.List[str]] = None
  92        self._type: t.Optional[DataType] = None
  93
  94        for arg_key, value in self.args.items():
  95            self._set_parent(arg_key, value)
  96
  97    def __eq__(self, other) -> bool:
  98        return type(self) is type(other) and _norm_args(self) == _norm_args(other)
  99
 100    def __hash__(self) -> int:
 101        return hash(
 102            (
 103                self.key,
 104                tuple(
 105                    (k, tuple(v) if isinstance(v, list) else v) for k, v in _norm_args(self).items()
 106                ),
 107            )
 108        )
 109
 110    @property
 111    def this(self):
 112        """
 113        Retrieves the argument with key "this".
 114        """
 115        return self.args.get("this")
 116
 117    @property
 118    def expression(self):
 119        """
 120        Retrieves the argument with key "expression".
 121        """
 122        return self.args.get("expression")
 123
 124    @property
 125    def expressions(self):
 126        """
 127        Retrieves the argument with key "expressions".
 128        """
 129        return self.args.get("expressions") or []
 130
 131    def text(self, key) -> str:
 132        """
 133        Returns a textual representation of the argument corresponding to "key". This can only be used
 134        for args that are strings or leaf Expression instances, such as identifiers and literals.
 135        """
 136        field = self.args.get(key)
 137        if isinstance(field, str):
 138            return field
 139        if isinstance(field, (Identifier, Literal, Var)):
 140            return field.this
 141        if isinstance(field, (Star, Null)):
 142            return field.name
 143        return ""
 144
 145    @property
 146    def is_string(self) -> bool:
 147        """
 148        Checks whether a Literal expression is a string.
 149        """
 150        return isinstance(self, Literal) and self.args["is_string"]
 151
 152    @property
 153    def is_number(self) -> bool:
 154        """
 155        Checks whether a Literal expression is a number.
 156        """
 157        return isinstance(self, Literal) and not self.args["is_string"]
 158
 159    @property
 160    def is_int(self) -> bool:
 161        """
 162        Checks whether a Literal expression is an integer.
 163        """
 164        if self.is_number:
 165            try:
 166                int(self.name)
 167                return True
 168            except ValueError:
 169                pass
 170        return False
 171
 172    @property
 173    def is_star(self) -> bool:
 174        """Checks whether an expression is a star."""
 175        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 176
 177    @property
 178    def alias(self) -> str:
 179        """
 180        Returns the alias of the expression, or an empty string if it's not aliased.
 181        """
 182        if isinstance(self.args.get("alias"), TableAlias):
 183            return self.args["alias"].name
 184        return self.text("alias")
 185
 186    @property
 187    def name(self) -> str:
 188        return self.text("this")
 189
 190    @property
 191    def alias_or_name(self):
 192        return self.alias or self.name
 193
 194    @property
 195    def output_name(self):
 196        """
 197        Name of the output column if this expression is a selection.
 198
 199        If the Expression has no output name, an empty string is returned.
 200
 201        Example:
 202            >>> from sqlglot import parse_one
 203            >>> parse_one("SELECT a").expressions[0].output_name
 204            'a'
 205            >>> parse_one("SELECT b AS c").expressions[0].output_name
 206            'c'
 207            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 208            ''
 209        """
 210        return ""
 211
 212    @property
 213    def type(self) -> t.Optional[DataType]:
 214        return self._type
 215
 216    @type.setter
 217    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 218        if dtype and not isinstance(dtype, DataType):
 219            dtype = DataType.build(dtype)
 220        self._type = dtype  # type: ignore
 221
 222    def __deepcopy__(self, memo):
 223        copy = self.__class__(**deepcopy(self.args))
 224        copy.comments = self.comments
 225        copy.type = self.type
 226        return copy
 227
 228    def copy(self):
 229        """
 230        Returns a deep copy of the expression.
 231        """
 232        new = deepcopy(self)
 233        new.parent = self.parent
 234        for item, parent, _ in new.bfs():
 235            if isinstance(item, Expression) and parent:
 236                item.parent = parent
 237        return new
 238
 239    def append(self, arg_key, value):
 240        """
 241        Appends value to arg_key if it's a list or sets it as a new list.
 242
 243        Args:
 244            arg_key (str): name of the list expression arg
 245            value (Any): value to append to the list
 246        """
 247        if not isinstance(self.args.get(arg_key), list):
 248            self.args[arg_key] = []
 249        self.args[arg_key].append(value)
 250        self._set_parent(arg_key, value)
 251
 252    def set(self, arg_key, value):
 253        """
 254        Sets `arg_key` to `value`.
 255
 256        Args:
 257            arg_key (str): name of the expression arg.
 258            value: value to set the arg to.
 259        """
 260        self.args[arg_key] = value
 261        self._set_parent(arg_key, value)
 262
 263    def _set_parent(self, arg_key, value):
 264        if isinstance(value, Expression):
 265            value.parent = self
 266            value.arg_key = arg_key
 267        elif isinstance(value, list):
 268            for v in value:
 269                if isinstance(v, Expression):
 270                    v.parent = self
 271                    v.arg_key = arg_key
 272
 273    @property
 274    def depth(self):
 275        """
 276        Returns the depth of this tree.
 277        """
 278        if self.parent:
 279            return self.parent.depth + 1
 280        return 0
 281
 282    def find(self, *expression_types, bfs=True):
 283        """
 284        Returns the first node in this tree which matches at least one of
 285        the specified types.
 286
 287        Args:
 288            expression_types (type): the expression type(s) to match.
 289
 290        Returns:
 291            The node which matches the criteria or None if no such node was found.
 292        """
 293        return next(self.find_all(*expression_types, bfs=bfs), None)
 294
 295    def find_all(self, *expression_types, bfs=True):
 296        """
 297        Returns a generator object which visits all nodes in this tree and only
 298        yields those that match at least one of the specified expression types.
 299
 300        Args:
 301            expression_types (type): the expression type(s) to match.
 302
 303        Returns:
 304            The generator object.
 305        """
 306        for expression, _, _ in self.walk(bfs=bfs):
 307            if isinstance(expression, expression_types):
 308                yield expression
 309
 310    def find_ancestor(self, *expression_types):
 311        """
 312        Returns a nearest parent matching expression_types.
 313
 314        Args:
 315            expression_types (type): the expression type(s) to match.
 316
 317        Returns:
 318            The parent node.
 319        """
 320        ancestor = self.parent
 321        while ancestor and not isinstance(ancestor, expression_types):
 322            ancestor = ancestor.parent
 323        return ancestor
 324
 325    @property
 326    def parent_select(self):
 327        """
 328        Returns the parent select statement.
 329        """
 330        return self.find_ancestor(Select)
 331
 332    def walk(self, bfs=True, prune=None):
 333        """
 334        Returns a generator object which visits all nodes in this tree.
 335
 336        Args:
 337            bfs (bool): if set to True the BFS traversal order will be applied,
 338                otherwise the DFS traversal will be used instead.
 339            prune ((node, parent, arg_key) -> bool): callable that returns True if
 340                the generator should stop traversing this branch of the tree.
 341
 342        Returns:
 343            the generator object.
 344        """
 345        if bfs:
 346            yield from self.bfs(prune=prune)
 347        else:
 348            yield from self.dfs(prune=prune)
 349
 350    def dfs(self, parent=None, key=None, prune=None):
 351        """
 352        Returns a generator object which visits all nodes in this tree in
 353        the DFS (Depth-first) order.
 354
 355        Returns:
 356            The generator object.
 357        """
 358        parent = parent or self.parent
 359        yield self, parent, key
 360        if prune and prune(self, parent, key):
 361            return
 362
 363        for k, v in self.args.items():
 364            for node in ensure_collection(v):
 365                if isinstance(node, Expression):
 366                    yield from node.dfs(self, k, prune)
 367
 368    def bfs(self, prune=None):
 369        """
 370        Returns a generator object which visits all nodes in this tree in
 371        the BFS (Breadth-first) order.
 372
 373        Returns:
 374            The generator object.
 375        """
 376        queue = deque([(self, self.parent, None)])
 377
 378        while queue:
 379            item, parent, key = queue.popleft()
 380
 381            yield item, parent, key
 382            if prune and prune(item, parent, key):
 383                continue
 384
 385            if isinstance(item, Expression):
 386                for k, v in item.args.items():
 387                    for node in ensure_collection(v):
 388                        if isinstance(node, Expression):
 389                            queue.append((node, item, k))
 390
 391    def unnest(self):
 392        """
 393        Returns the first non parenthesis child or self.
 394        """
 395        expression = self
 396        while isinstance(expression, Paren):
 397            expression = expression.this
 398        return expression
 399
 400    def unalias(self):
 401        """
 402        Returns the inner expression if this is an Alias.
 403        """
 404        if isinstance(self, Alias):
 405            return self.this
 406        return self
 407
 408    def unnest_operands(self):
 409        """
 410        Returns unnested operands as a tuple.
 411        """
 412        return tuple(arg.unnest() for arg in self.args.values() if arg)
 413
 414    def flatten(self, unnest=True):
 415        """
 416        Returns a generator which yields child nodes who's parents are the same class.
 417
 418        A AND B AND C -> [A, B, C]
 419        """
 420        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not isinstance(n, self.__class__)):
 421            if not isinstance(node, self.__class__):
 422                yield node.unnest() if unnest else node
 423
 424    def __str__(self):
 425        return self.sql()
 426
 427    def __repr__(self):
 428        return self._to_s()
 429
 430    def sql(self, dialect: DialectType = None, **opts) -> str:
 431        """
 432        Returns SQL string representation of this tree.
 433
 434        Args:
 435            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 436            opts: other `sqlglot.generator.Generator` options.
 437
 438        Returns:
 439            The SQL string.
 440        """
 441        from sqlglot.dialects import Dialect
 442
 443        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 444
 445    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 446        indent = "" if not level else "\n"
 447        indent += "".join(["  "] * level)
 448        left = f"({self.key.upper()} "
 449
 450        args: t.Dict[str, t.Any] = {
 451            k: ", ".join(
 452                v._to_s(hide_missing=hide_missing, level=level + 1)
 453                if hasattr(v, "_to_s")
 454                else str(v)
 455                for v in ensure_collection(vs)
 456                if v is not None
 457            )
 458            for k, vs in self.args.items()
 459        }
 460        args["comments"] = self.comments
 461        args["type"] = self.type
 462        args = {k: v for k, v in args.items() if v or not hide_missing}
 463
 464        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 465        right += ")"
 466
 467        return indent + left + right
 468
 469    def transform(self, fun, *args, copy=True, **kwargs):
 470        """
 471        Recursively visits all tree nodes (excluding already transformed ones)
 472        and applies the given transformation function to each node.
 473
 474        Args:
 475            fun (function): a function which takes a node as an argument and returns a
 476                new transformed node or the same node without modifications. If the function
 477                returns None, then the corresponding node will be removed from the syntax tree.
 478            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 479                modified in place.
 480
 481        Returns:
 482            The transformed tree.
 483        """
 484        node = self.copy() if copy else self
 485        new_node = fun(node, *args, **kwargs)
 486
 487        if new_node is None or not isinstance(new_node, Expression):
 488            return new_node
 489        if new_node is not node:
 490            new_node.parent = node.parent
 491            return new_node
 492
 493        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 494        return new_node
 495
 496    def replace(self, expression):
 497        """
 498        Swap out this expression with a new expression.
 499
 500        For example::
 501
 502            >>> tree = Select().select("x").from_("tbl")
 503            >>> tree.find(Column).replace(Column(this="y"))
 504            (COLUMN this: y)
 505            >>> tree.sql()
 506            'SELECT y FROM tbl'
 507
 508        Args:
 509            expression (Expression|None): new node
 510
 511        Returns:
 512            The new expression or expressions.
 513        """
 514        if not self.parent:
 515            return expression
 516
 517        parent = self.parent
 518        self.parent = None
 519
 520        replace_children(parent, lambda child: expression if child is self else child)
 521        return expression
 522
 523    def pop(self):
 524        """
 525        Remove this expression from its AST.
 526        """
 527        self.replace(None)
 528
 529    def assert_is(self, type_):
 530        """
 531        Assert that this `Expression` is an instance of `type_`.
 532
 533        If it is NOT an instance of `type_`, this raises an assertion error.
 534        Otherwise, this returns this expression.
 535
 536        Examples:
 537            This is useful for type security in chained expressions:
 538
 539            >>> import sqlglot
 540            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 541            'SELECT x, z FROM y'
 542        """
 543        assert isinstance(self, type_)
 544        return self
 545
 546    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 547        """
 548        Checks if this expression is valid (e.g. all mandatory args are set).
 549
 550        Args:
 551            args: a sequence of values that were used to instantiate a Func expression. This is used
 552                to check that the provided arguments don't exceed the function argument limit.
 553
 554        Returns:
 555            A list of error messages for all possible errors that were found.
 556        """
 557        errors: t.List[str] = []
 558
 559        for k in self.args:
 560            if k not in self.arg_types:
 561                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 562        for k, mandatory in self.arg_types.items():
 563            v = self.args.get(k)
 564            if mandatory and (v is None or (isinstance(v, list) and not v)):
 565                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 566
 567        if (
 568            args
 569            and isinstance(self, Func)
 570            and len(args) > len(self.arg_types)
 571            and not self.is_var_len_args
 572        ):
 573            errors.append(
 574                f"The number of provided arguments ({len(args)}) is greater than "
 575                f"the maximum number of supported arguments ({len(self.arg_types)})"
 576            )
 577
 578        return errors
 579
 580    def dump(self):
 581        """
 582        Dump this Expression to a JSON-serializable dict.
 583        """
 584        from sqlglot.serde import dump
 585
 586        return dump(self)
 587
 588    @classmethod
 589    def load(cls, obj):
 590        """
 591        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 592        """
 593        from sqlglot.serde import load
 594
 595        return load(obj)
 596
 597
 598IntoType = t.Union[
 599    str,
 600    t.Type[Expression],
 601    t.Collection[t.Union[str, t.Type[Expression]]],
 602]
 603
 604
 605class Condition(Expression):
 606    def and_(self, *expressions, dialect=None, **opts):
 607        """
 608        AND this condition with one or multiple expressions.
 609
 610        Example:
 611            >>> condition("x=1").and_("y=1").sql()
 612            'x = 1 AND y = 1'
 613
 614        Args:
 615            *expressions (str | Expression): the SQL code strings to parse.
 616                If an `Expression` instance is passed, it will be used as-is.
 617            dialect (str): the dialect used to parse the input expression.
 618            opts (kwargs): other options to use to parse the input expressions.
 619
 620        Returns:
 621            And: the new condition.
 622        """
 623        return and_(self, *expressions, dialect=dialect, **opts)
 624
 625    def or_(self, *expressions, dialect=None, **opts):
 626        """
 627        OR this condition with one or multiple expressions.
 628
 629        Example:
 630            >>> condition("x=1").or_("y=1").sql()
 631            'x = 1 OR y = 1'
 632
 633        Args:
 634            *expressions (str | Expression): the SQL code strings to parse.
 635                If an `Expression` instance is passed, it will be used as-is.
 636            dialect (str): the dialect used to parse the input expression.
 637            opts (kwargs): other options to use to parse the input expressions.
 638
 639        Returns:
 640            Or: the new condition.
 641        """
 642        return or_(self, *expressions, dialect=dialect, **opts)
 643
 644    def not_(self):
 645        """
 646        Wrap this condition with NOT.
 647
 648        Example:
 649            >>> condition("x=1").not_().sql()
 650            'NOT x = 1'
 651
 652        Returns:
 653            Not: the new condition.
 654        """
 655        return not_(self)
 656
 657
 658class Predicate(Condition):
 659    """Relationships like x = y, x > 1, x >= y."""
 660
 661
 662class DerivedTable(Expression):
 663    @property
 664    def alias_column_names(self):
 665        table_alias = self.args.get("alias")
 666        if not table_alias:
 667            return []
 668        column_list = table_alias.assert_is(TableAlias).args.get("columns") or []
 669        return [c.name for c in column_list]
 670
 671    @property
 672    def selects(self):
 673        alias = self.args.get("alias")
 674
 675        if alias:
 676            return alias.columns
 677        return []
 678
 679    @property
 680    def named_selects(self):
 681        return [select.output_name for select in self.selects]
 682
 683
 684class Unionable(Expression):
 685    def union(self, expression, distinct=True, dialect=None, **opts):
 686        """
 687        Builds a UNION expression.
 688
 689        Example:
 690            >>> import sqlglot
 691            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 692            'SELECT * FROM foo UNION SELECT * FROM bla'
 693
 694        Args:
 695            expression (str | Expression): the SQL code string.
 696                If an `Expression` instance is passed, it will be used as-is.
 697            distinct (bool): set the DISTINCT flag if and only if this is true.
 698            dialect (str): the dialect used to parse the input expression.
 699            opts (kwargs): other options to use to parse the input expressions.
 700        Returns:
 701            Union: the Union expression.
 702        """
 703        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 704
 705    def intersect(self, expression, distinct=True, dialect=None, **opts):
 706        """
 707        Builds an INTERSECT expression.
 708
 709        Example:
 710            >>> import sqlglot
 711            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 712            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 713
 714        Args:
 715            expression (str | Expression): the SQL code string.
 716                If an `Expression` instance is passed, it will be used as-is.
 717            distinct (bool): set the DISTINCT flag if and only if this is true.
 718            dialect (str): the dialect used to parse the input expression.
 719            opts (kwargs): other options to use to parse the input expressions.
 720        Returns:
 721            Intersect: the Intersect expression
 722        """
 723        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 724
 725    def except_(self, expression, distinct=True, dialect=None, **opts):
 726        """
 727        Builds an EXCEPT expression.
 728
 729        Example:
 730            >>> import sqlglot
 731            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 732            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 733
 734        Args:
 735            expression (str | Expression): the SQL code string.
 736                If an `Expression` instance is passed, it will be used as-is.
 737            distinct (bool): set the DISTINCT flag if and only if this is true.
 738            dialect (str): the dialect used to parse the input expression.
 739            opts (kwargs): other options to use to parse the input expressions.
 740        Returns:
 741            Except: the Except expression
 742        """
 743        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 744
 745
 746class UDTF(DerivedTable, Unionable):
 747    pass
 748
 749
 750class Cache(Expression):
 751    arg_types = {
 752        "with": False,
 753        "this": True,
 754        "lazy": False,
 755        "options": False,
 756        "expression": False,
 757    }
 758
 759
 760class Uncache(Expression):
 761    arg_types = {"this": True, "exists": False}
 762
 763
 764class Create(Expression):
 765    arg_types = {
 766        "with": False,
 767        "this": True,
 768        "kind": True,
 769        "expression": False,
 770        "set": False,
 771        "multiset": False,
 772        "global_temporary": False,
 773        "volatile": False,
 774        "exists": False,
 775        "properties": False,
 776        "temporary": False,
 777        "transient": False,
 778        "external": False,
 779        "replace": False,
 780        "unique": False,
 781        "materialized": False,
 782        "data": False,
 783        "statistics": False,
 784        "no_primary_index": False,
 785        "indexes": False,
 786        "no_schema_binding": False,
 787        "begin": False,
 788    }
 789
 790
 791class Describe(Expression):
 792    arg_types = {"this": True, "kind": False}
 793
 794
 795class Set(Expression):
 796    arg_types = {"expressions": True}
 797
 798
 799class SetItem(Expression):
 800    arg_types = {
 801        "this": False,
 802        "expressions": False,
 803        "kind": False,
 804        "collate": False,  # MySQL SET NAMES statement
 805        "global": False,
 806    }
 807
 808
 809class Show(Expression):
 810    arg_types = {
 811        "this": True,
 812        "target": False,
 813        "offset": False,
 814        "limit": False,
 815        "like": False,
 816        "where": False,
 817        "db": False,
 818        "full": False,
 819        "mutex": False,
 820        "query": False,
 821        "channel": False,
 822        "global": False,
 823        "log": False,
 824        "position": False,
 825        "types": False,
 826    }
 827
 828
 829class UserDefinedFunction(Expression):
 830    arg_types = {"this": True, "expressions": False, "wrapped": False}
 831
 832
 833class CharacterSet(Expression):
 834    arg_types = {"this": True, "default": False}
 835
 836
 837class With(Expression):
 838    arg_types = {"expressions": True, "recursive": False}
 839
 840    @property
 841    def recursive(self) -> bool:
 842        return bool(self.args.get("recursive"))
 843
 844
 845class WithinGroup(Expression):
 846    arg_types = {"this": True, "expression": False}
 847
 848
 849class CTE(DerivedTable):
 850    arg_types = {"this": True, "alias": True}
 851
 852
 853class TableAlias(Expression):
 854    arg_types = {"this": False, "columns": False}
 855
 856    @property
 857    def columns(self):
 858        return self.args.get("columns") or []
 859
 860
 861class BitString(Condition):
 862    pass
 863
 864
 865class HexString(Condition):
 866    pass
 867
 868
 869class ByteString(Condition):
 870    pass
 871
 872
 873class Column(Condition):
 874    arg_types = {"this": True, "table": False, "db": False, "catalog": False}
 875
 876    @property
 877    def table(self) -> str:
 878        return self.text("table")
 879
 880    @property
 881    def db(self) -> str:
 882        return self.text("db")
 883
 884    @property
 885    def catalog(self) -> str:
 886        return self.text("catalog")
 887
 888    @property
 889    def output_name(self) -> str:
 890        return self.name
 891
 892
 893class ColumnDef(Expression):
 894    arg_types = {
 895        "this": True,
 896        "kind": False,
 897        "constraints": False,
 898        "exists": False,
 899    }
 900
 901
 902class AlterColumn(Expression):
 903    arg_types = {
 904        "this": True,
 905        "dtype": False,
 906        "collate": False,
 907        "using": False,
 908        "default": False,
 909        "drop": False,
 910    }
 911
 912
 913class RenameTable(Expression):
 914    pass
 915
 916
 917class ColumnConstraint(Expression):
 918    arg_types = {"this": False, "kind": True}
 919
 920
 921class ColumnConstraintKind(Expression):
 922    pass
 923
 924
 925class AutoIncrementColumnConstraint(ColumnConstraintKind):
 926    pass
 927
 928
 929class CaseSpecificColumnConstraint(ColumnConstraintKind):
 930    arg_types = {"not_": True}
 931
 932
 933class CharacterSetColumnConstraint(ColumnConstraintKind):
 934    arg_types = {"this": True}
 935
 936
 937class CheckColumnConstraint(ColumnConstraintKind):
 938    pass
 939
 940
 941class CollateColumnConstraint(ColumnConstraintKind):
 942    pass
 943
 944
 945class CommentColumnConstraint(ColumnConstraintKind):
 946    pass
 947
 948
 949class CompressColumnConstraint(ColumnConstraintKind):
 950    pass
 951
 952
 953class DateFormatColumnConstraint(ColumnConstraintKind):
 954    arg_types = {"this": True}
 955
 956
 957class DefaultColumnConstraint(ColumnConstraintKind):
 958    pass
 959
 960
 961class EncodeColumnConstraint(ColumnConstraintKind):
 962    pass
 963
 964
 965class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
 966    # this: True -> ALWAYS, this: False -> BY DEFAULT
 967    arg_types = {
 968        "this": False,
 969        "start": False,
 970        "increment": False,
 971        "minvalue": False,
 972        "maxvalue": False,
 973        "cycle": False,
 974    }
 975
 976
 977class InlineLengthColumnConstraint(ColumnConstraintKind):
 978    pass
 979
 980
 981class NotNullColumnConstraint(ColumnConstraintKind):
 982    arg_types = {"allow_null": False}
 983
 984
 985class PrimaryKeyColumnConstraint(ColumnConstraintKind):
 986    arg_types = {"desc": False}
 987
 988
 989class TitleColumnConstraint(ColumnConstraintKind):
 990    pass
 991
 992
 993class UniqueColumnConstraint(ColumnConstraintKind):
 994    arg_types: t.Dict[str, t.Any] = {}
 995
 996
 997class UppercaseColumnConstraint(ColumnConstraintKind):
 998    arg_types: t.Dict[str, t.Any] = {}
 999
1000
1001class PathColumnConstraint(ColumnConstraintKind):
1002    pass
1003
1004
1005class Constraint(Expression):
1006    arg_types = {"this": True, "expressions": True}
1007
1008
1009class Delete(Expression):
1010    arg_types = {"with": False, "this": False, "using": False, "where": False}
1011
1012
1013class Drop(Expression):
1014    arg_types = {
1015        "this": False,
1016        "kind": False,
1017        "exists": False,
1018        "temporary": False,
1019        "materialized": False,
1020        "cascade": False,
1021    }
1022
1023
1024class Filter(Expression):
1025    arg_types = {"this": True, "expression": True}
1026
1027
1028class Check(Expression):
1029    pass
1030
1031
1032class Directory(Expression):
1033    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1034    arg_types = {"this": True, "local": False, "row_format": False}
1035
1036
1037class ForeignKey(Expression):
1038    arg_types = {
1039        "expressions": True,
1040        "reference": False,
1041        "delete": False,
1042        "update": False,
1043    }
1044
1045
1046class PrimaryKey(Expression):
1047    arg_types = {"expressions": True, "options": False}
1048
1049
1050class Unique(Expression):
1051    arg_types = {"expressions": True}
1052
1053
1054# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1055# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1056class Into(Expression):
1057    arg_types = {"this": True, "temporary": False, "unlogged": False}
1058
1059
1060class From(Expression):
1061    arg_types = {"expressions": True}
1062
1063
1064class Having(Expression):
1065    pass
1066
1067
1068class Hint(Expression):
1069    arg_types = {"expressions": True}
1070
1071
1072class JoinHint(Expression):
1073    arg_types = {"this": True, "expressions": True}
1074
1075
1076class Identifier(Expression):
1077    arg_types = {"this": True, "quoted": False}
1078
1079    @property
1080    def quoted(self):
1081        return bool(self.args.get("quoted"))
1082
1083    def __eq__(self, other):
1084        return isinstance(other, self.__class__) and _norm_arg(self.this) == _norm_arg(other.this)
1085
1086    def __hash__(self):
1087        return hash((self.key, self.this.lower()))
1088
1089    @property
1090    def output_name(self):
1091        return self.name
1092
1093
1094class Index(Expression):
1095    arg_types = {
1096        "this": False,
1097        "table": False,
1098        "where": False,
1099        "columns": False,
1100        "unique": False,
1101        "primary": False,
1102        "amp": False,  # teradata
1103    }
1104
1105
1106class Insert(Expression):
1107    arg_types = {
1108        "with": False,
1109        "this": True,
1110        "expression": False,
1111        "overwrite": False,
1112        "exists": False,
1113        "partition": False,
1114        "alternative": False,
1115    }
1116
1117
1118# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1119class Introducer(Expression):
1120    arg_types = {"this": True, "expression": True}
1121
1122
1123# national char, like n'utf8'
1124class National(Expression):
1125    pass
1126
1127
1128class LoadData(Expression):
1129    arg_types = {
1130        "this": True,
1131        "local": False,
1132        "overwrite": False,
1133        "inpath": True,
1134        "partition": False,
1135        "input_format": False,
1136        "serde": False,
1137    }
1138
1139
1140class Partition(Expression):
1141    arg_types = {"expressions": True}
1142
1143
1144class Fetch(Expression):
1145    arg_types = {"direction": False, "count": False}
1146
1147
1148class Group(Expression):
1149    arg_types = {
1150        "expressions": False,
1151        "grouping_sets": False,
1152        "cube": False,
1153        "rollup": False,
1154    }
1155
1156
1157class Lambda(Expression):
1158    arg_types = {"this": True, "expressions": True}
1159
1160
1161class Limit(Expression):
1162    arg_types = {"this": False, "expression": True}
1163
1164
1165class Literal(Condition):
1166    arg_types = {"this": True, "is_string": True}
1167
1168    def __eq__(self, other):
1169        return (
1170            isinstance(other, Literal)
1171            and self.this == other.this
1172            and self.args["is_string"] == other.args["is_string"]
1173        )
1174
1175    def __hash__(self):
1176        return hash((self.key, self.this, self.args["is_string"]))
1177
1178    @classmethod
1179    def number(cls, number) -> Literal:
1180        return cls(this=str(number), is_string=False)
1181
1182    @classmethod
1183    def string(cls, string) -> Literal:
1184        return cls(this=str(string), is_string=True)
1185
1186    @property
1187    def output_name(self):
1188        return self.name
1189
1190
1191class Join(Expression):
1192    arg_types = {
1193        "this": True,
1194        "on": False,
1195        "side": False,
1196        "kind": False,
1197        "using": False,
1198        "natural": False,
1199    }
1200
1201    @property
1202    def kind(self):
1203        return self.text("kind").upper()
1204
1205    @property
1206    def side(self):
1207        return self.text("side").upper()
1208
1209    @property
1210    def alias_or_name(self):
1211        return self.this.alias_or_name
1212
1213    def on(self, *expressions, append=True, dialect=None, copy=True, **opts):
1214        """
1215        Append to or set the ON expressions.
1216
1217        Example:
1218            >>> import sqlglot
1219            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1220            'JOIN x ON y = 1'
1221
1222        Args:
1223            *expressions (str | Expression): the SQL code strings to parse.
1224                If an `Expression` instance is passed, it will be used as-is.
1225                Multiple expressions are combined with an AND operator.
1226            append (bool): if `True`, AND the new expressions to any existing expression.
1227                Otherwise, this resets the expression.
1228            dialect (str): the dialect used to parse the input expressions.
1229            copy (bool): if `False`, modify this expression instance in-place.
1230            opts (kwargs): other options to use to parse the input expressions.
1231
1232        Returns:
1233            Join: the modified join expression.
1234        """
1235        join = _apply_conjunction_builder(
1236            *expressions,
1237            instance=self,
1238            arg="on",
1239            append=append,
1240            dialect=dialect,
1241            copy=copy,
1242            **opts,
1243        )
1244
1245        if join.kind == "CROSS":
1246            join.set("kind", None)
1247
1248        return join
1249
1250    def using(self, *expressions, append=True, dialect=None, copy=True, **opts):
1251        """
1252        Append to or set the USING expressions.
1253
1254        Example:
1255            >>> import sqlglot
1256            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1257            'JOIN x USING (foo, bla)'
1258
1259        Args:
1260            *expressions (str | Expression): the SQL code strings to parse.
1261                If an `Expression` instance is passed, it will be used as-is.
1262            append (bool): if `True`, concatenate the new expressions to the existing "using" list.
1263                Otherwise, this resets the expression.
1264            dialect (str): the dialect used to parse the input expressions.
1265            copy (bool): if `False`, modify this expression instance in-place.
1266            opts (kwargs): other options to use to parse the input expressions.
1267
1268        Returns:
1269            Join: the modified join expression.
1270        """
1271        join = _apply_list_builder(
1272            *expressions,
1273            instance=self,
1274            arg="using",
1275            append=append,
1276            dialect=dialect,
1277            copy=copy,
1278            **opts,
1279        )
1280
1281        if join.kind == "CROSS":
1282            join.set("kind", None)
1283
1284        return join
1285
1286
1287class Lateral(UDTF):
1288    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1289
1290
1291class MatchRecognize(Expression):
1292    arg_types = {
1293        "partition_by": False,
1294        "order": False,
1295        "measures": False,
1296        "rows": False,
1297        "after": False,
1298        "pattern": False,
1299        "define": False,
1300    }
1301
1302
1303# Clickhouse FROM FINAL modifier
1304# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1305class Final(Expression):
1306    pass
1307
1308
1309class Offset(Expression):
1310    arg_types = {"this": False, "expression": True}
1311
1312
1313class Order(Expression):
1314    arg_types = {"this": False, "expressions": True}
1315
1316
1317# hive specific sorts
1318# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1319class Cluster(Order):
1320    pass
1321
1322
1323class Distribute(Order):
1324    pass
1325
1326
1327class Sort(Order):
1328    pass
1329
1330
1331class Ordered(Expression):
1332    arg_types = {"this": True, "desc": True, "nulls_first": True}
1333
1334
1335class Property(Expression):
1336    arg_types = {"this": True, "value": True}
1337
1338
1339class AlgorithmProperty(Property):
1340    arg_types = {"this": True}
1341
1342
1343class DefinerProperty(Property):
1344    arg_types = {"this": True}
1345
1346
1347class SqlSecurityProperty(Property):
1348    arg_types = {"definer": True}
1349
1350
1351class TableFormatProperty(Property):
1352    arg_types = {"this": True}
1353
1354
1355class PartitionedByProperty(Property):
1356    arg_types = {"this": True}
1357
1358
1359class FileFormatProperty(Property):
1360    arg_types = {"this": True}
1361
1362
1363class DistKeyProperty(Property):
1364    arg_types = {"this": True}
1365
1366
1367class SortKeyProperty(Property):
1368    arg_types = {"this": True, "compound": False}
1369
1370
1371class DistStyleProperty(Property):
1372    arg_types = {"this": True}
1373
1374
1375class LikeProperty(Property):
1376    arg_types = {"this": True, "expressions": False}
1377
1378
1379class LocationProperty(Property):
1380    arg_types = {"this": True}
1381
1382
1383class EngineProperty(Property):
1384    arg_types = {"this": True}
1385
1386
1387class AutoIncrementProperty(Property):
1388    arg_types = {"this": True}
1389
1390
1391class CharacterSetProperty(Property):
1392    arg_types = {"this": True, "default": True}
1393
1394
1395class CollateProperty(Property):
1396    arg_types = {"this": True}
1397
1398
1399class SchemaCommentProperty(Property):
1400    arg_types = {"this": True}
1401
1402
1403class ReturnsProperty(Property):
1404    arg_types = {"this": True, "is_table": False, "table": False}
1405
1406
1407class LanguageProperty(Property):
1408    arg_types = {"this": True}
1409
1410
1411class ExecuteAsProperty(Property):
1412    arg_types = {"this": True}
1413
1414
1415class VolatilityProperty(Property):
1416    arg_types = {"this": True}
1417
1418
1419class RowFormatDelimitedProperty(Property):
1420    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
1421    arg_types = {
1422        "fields": False,
1423        "escaped": False,
1424        "collection_items": False,
1425        "map_keys": False,
1426        "lines": False,
1427        "null": False,
1428        "serde": False,
1429    }
1430
1431
1432class RowFormatSerdeProperty(Property):
1433    arg_types = {"this": True}
1434
1435
1436class SerdeProperties(Property):
1437    arg_types = {"expressions": True}
1438
1439
1440class FallbackProperty(Property):
1441    arg_types = {"no": True, "protection": False}
1442
1443
1444class WithJournalTableProperty(Property):
1445    arg_types = {"this": True}
1446
1447
1448class LogProperty(Property):
1449    arg_types = {"no": True}
1450
1451
1452class JournalProperty(Property):
1453    arg_types = {"no": True, "dual": False, "before": False}
1454
1455
1456class AfterJournalProperty(Property):
1457    arg_types = {"no": True, "dual": False, "local": False}
1458
1459
1460class ChecksumProperty(Property):
1461    arg_types = {"on": False, "default": False}
1462
1463
1464class FreespaceProperty(Property):
1465    arg_types = {"this": True, "percent": False}
1466
1467
1468class MergeBlockRatioProperty(Property):
1469    arg_types = {"this": False, "no": False, "default": False, "percent": False}
1470
1471
1472class DataBlocksizeProperty(Property):
1473    arg_types = {"size": False, "units": False, "min": False, "default": False}
1474
1475
1476class BlockCompressionProperty(Property):
1477    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1478
1479
1480class IsolatedLoadingProperty(Property):
1481    arg_types = {
1482        "no": True,
1483        "concurrent": True,
1484        "for_all": True,
1485        "for_insert": True,
1486        "for_none": True,
1487    }
1488
1489
1490class LockingProperty(Property):
1491    arg_types = {
1492        "this": False,
1493        "kind": True,
1494        "for_or_in": True,
1495        "lock_type": True,
1496        "override": False,
1497    }
1498
1499
1500class Properties(Expression):
1501    arg_types = {"expressions": True}
1502
1503    NAME_TO_PROPERTY = {
1504        "ALGORITHM": AlgorithmProperty,
1505        "AUTO_INCREMENT": AutoIncrementProperty,
1506        "CHARACTER SET": CharacterSetProperty,
1507        "COLLATE": CollateProperty,
1508        "COMMENT": SchemaCommentProperty,
1509        "DEFINER": DefinerProperty,
1510        "DISTKEY": DistKeyProperty,
1511        "DISTSTYLE": DistStyleProperty,
1512        "ENGINE": EngineProperty,
1513        "EXECUTE AS": ExecuteAsProperty,
1514        "FORMAT": FileFormatProperty,
1515        "LANGUAGE": LanguageProperty,
1516        "LOCATION": LocationProperty,
1517        "PARTITIONED_BY": PartitionedByProperty,
1518        "RETURNS": ReturnsProperty,
1519        "SORTKEY": SortKeyProperty,
1520        "TABLE_FORMAT": TableFormatProperty,
1521    }
1522
1523    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
1524
1525    # CREATE property locations
1526    # Form: schema specified
1527    #   create [POST_CREATE]
1528    #     table a [POST_NAME]
1529    #     (b int) [POST_SCHEMA]
1530    #     with ([POST_WITH])
1531    #     index (b) [POST_INDEX]
1532    #
1533    # Form: alias selection
1534    #   create [POST_CREATE]
1535    #     table a [POST_NAME]
1536    #     as [POST_ALIAS] (select * from b)
1537    #     index (c) [POST_INDEX]
1538    class Location(AutoName):
1539        POST_CREATE = auto()
1540        POST_NAME = auto()
1541        POST_SCHEMA = auto()
1542        POST_WITH = auto()
1543        POST_ALIAS = auto()
1544        POST_INDEX = auto()
1545        UNSUPPORTED = auto()
1546
1547    @classmethod
1548    def from_dict(cls, properties_dict) -> Properties:
1549        expressions = []
1550        for key, value in properties_dict.items():
1551            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
1552            if property_cls:
1553                expressions.append(property_cls(this=convert(value)))
1554            else:
1555                expressions.append(Property(this=Literal.string(key), value=convert(value)))
1556
1557        return cls(expressions=expressions)
1558
1559
1560class Qualify(Expression):
1561    pass
1562
1563
1564# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
1565class Return(Expression):
1566    pass
1567
1568
1569class Reference(Expression):
1570    arg_types = {"this": True, "expressions": False, "options": False}
1571
1572
1573class Tuple(Expression):
1574    arg_types = {"expressions": False}
1575
1576
1577class Subqueryable(Unionable):
1578    def subquery(self, alias=None, copy=True) -> Subquery:
1579        """
1580        Convert this expression to an aliased expression that can be used as a Subquery.
1581
1582        Example:
1583            >>> subquery = Select().select("x").from_("tbl").subquery()
1584            >>> Select().select("x").from_(subquery).sql()
1585            'SELECT x FROM (SELECT x FROM tbl)'
1586
1587        Args:
1588            alias (str | Identifier): an optional alias for the subquery
1589            copy (bool): if `False`, modify this expression instance in-place.
1590
1591        Returns:
1592            Alias: the subquery
1593        """
1594        instance = _maybe_copy(self, copy)
1595        return Subquery(
1596            this=instance,
1597            alias=TableAlias(this=to_identifier(alias)),
1598        )
1599
1600    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1601        raise NotImplementedError
1602
1603    @property
1604    def ctes(self):
1605        with_ = self.args.get("with")
1606        if not with_:
1607            return []
1608        return with_.expressions
1609
1610    @property
1611    def selects(self):
1612        raise NotImplementedError("Subqueryable objects must implement `selects`")
1613
1614    @property
1615    def named_selects(self):
1616        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
1617
1618    def with_(
1619        self,
1620        alias,
1621        as_,
1622        recursive=None,
1623        append=True,
1624        dialect=None,
1625        copy=True,
1626        **opts,
1627    ):
1628        """
1629        Append to or set the common table expressions.
1630
1631        Example:
1632            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1633            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1634
1635        Args:
1636            alias (str | Expression): the SQL code string to parse as the table name.
1637                If an `Expression` instance is passed, this is used as-is.
1638            as_ (str | Expression): the SQL code string to parse as the table expression.
1639                If an `Expression` instance is passed, it will be used as-is.
1640            recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`.
1641            append (bool): if `True`, add to any existing expressions.
1642                Otherwise, this resets the expressions.
1643            dialect (str): the dialect used to parse the input expression.
1644            copy (bool): if `False`, modify this expression instance in-place.
1645            opts (kwargs): other options to use to parse the input expressions.
1646
1647        Returns:
1648            Select: the modified expression.
1649        """
1650        alias_expression = maybe_parse(
1651            alias,
1652            dialect=dialect,
1653            into=TableAlias,
1654            **opts,
1655        )
1656        as_expression = maybe_parse(
1657            as_,
1658            dialect=dialect,
1659            **opts,
1660        )
1661        cte = CTE(
1662            this=as_expression,
1663            alias=alias_expression,
1664        )
1665        return _apply_child_list_builder(
1666            cte,
1667            instance=self,
1668            arg="with",
1669            append=append,
1670            copy=copy,
1671            into=With,
1672            properties={"recursive": recursive or False},
1673        )
1674
1675
1676QUERY_MODIFIERS = {
1677    "match": False,
1678    "laterals": False,
1679    "joins": False,
1680    "pivots": False,
1681    "where": False,
1682    "group": False,
1683    "having": False,
1684    "qualify": False,
1685    "windows": False,
1686    "distribute": False,
1687    "sort": False,
1688    "cluster": False,
1689    "order": False,
1690    "limit": False,
1691    "offset": False,
1692    "lock": False,
1693}
1694
1695
1696class Table(Expression):
1697    arg_types = {
1698        "this": True,
1699        "alias": False,
1700        "db": False,
1701        "catalog": False,
1702        "laterals": False,
1703        "joins": False,
1704        "pivots": False,
1705        "hints": False,
1706        "system_time": False,
1707    }
1708
1709    @property
1710    def db(self) -> str:
1711        return self.text("db")
1712
1713    @property
1714    def catalog(self) -> str:
1715        return self.text("catalog")
1716
1717
1718# See the TSQL "Querying data in a system-versioned temporal table" page
1719class SystemTime(Expression):
1720    arg_types = {
1721        "this": False,
1722        "expression": False,
1723        "kind": True,
1724    }
1725
1726
1727class Union(Subqueryable):
1728    arg_types = {
1729        "with": False,
1730        "this": True,
1731        "expression": True,
1732        "distinct": False,
1733        **QUERY_MODIFIERS,
1734    }
1735
1736    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1737        """
1738        Set the LIMIT expression.
1739
1740        Example:
1741            >>> select("1").union(select("1")).limit(1).sql()
1742            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
1743
1744        Args:
1745            expression (str | int | Expression): the SQL code string to parse.
1746                This can also be an integer.
1747                If a `Limit` instance is passed, this is used as-is.
1748                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1749            dialect (str): the dialect used to parse the input expression.
1750            copy (bool): if `False`, modify this expression instance in-place.
1751            opts (kwargs): other options to use to parse the input expressions.
1752
1753        Returns:
1754            Select: The limited subqueryable.
1755        """
1756        return (
1757            select("*")
1758            .from_(self.subquery(alias="_l_0", copy=copy))
1759            .limit(expression, dialect=dialect, copy=False, **opts)
1760        )
1761
1762    def select(
1763        self,
1764        *expressions: str | Expression,
1765        append: bool = True,
1766        dialect: DialectType = None,
1767        copy: bool = True,
1768        **opts,
1769    ) -> Union:
1770        """Append to or set the SELECT of the union recursively.
1771
1772        Example:
1773            >>> from sqlglot import parse_one
1774            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
1775            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
1776
1777        Args:
1778            *expressions: the SQL code strings to parse.
1779                If an `Expression` instance is passed, it will be used as-is.
1780            append: if `True`, add to any existing expressions.
1781                Otherwise, this resets the expressions.
1782            dialect: the dialect used to parse the input expressions.
1783            copy: if `False`, modify this expression instance in-place.
1784            opts: other options to use to parse the input expressions.
1785
1786        Returns:
1787            Union: the modified expression.
1788        """
1789        this = self.copy() if copy else self
1790        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
1791        this.expression.unnest().select(
1792            *expressions, append=append, dialect=dialect, copy=False, **opts
1793        )
1794        return this
1795
1796    @property
1797    def named_selects(self):
1798        return self.this.unnest().named_selects
1799
1800    @property
1801    def selects(self):
1802        return self.this.unnest().selects
1803
1804    @property
1805    def left(self):
1806        return self.this
1807
1808    @property
1809    def right(self):
1810        return self.expression
1811
1812
1813class Except(Union):
1814    pass
1815
1816
1817class Intersect(Union):
1818    pass
1819
1820
1821class Unnest(UDTF):
1822    arg_types = {
1823        "expressions": True,
1824        "ordinality": False,
1825        "alias": False,
1826        "offset": False,
1827    }
1828
1829
1830class Update(Expression):
1831    arg_types = {
1832        "with": False,
1833        "this": False,
1834        "expressions": True,
1835        "from": False,
1836        "where": False,
1837    }
1838
1839
1840class Values(UDTF):
1841    arg_types = {
1842        "expressions": True,
1843        "ordinality": False,
1844        "alias": False,
1845    }
1846
1847
1848class Var(Expression):
1849    pass
1850
1851
1852class Schema(Expression):
1853    arg_types = {"this": False, "expressions": False}
1854
1855
1856# Used to represent the FOR UPDATE and FOR SHARE locking read types.
1857# https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
1858class Lock(Expression):
1859    arg_types = {"update": True}
1860
1861
1862class Select(Subqueryable):
1863    arg_types = {
1864        "with": False,
1865        "expressions": False,
1866        "hint": False,
1867        "distinct": False,
1868        "into": False,
1869        "from": False,
1870        **QUERY_MODIFIERS,
1871    }
1872
1873    def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1874        """
1875        Set the FROM expression.
1876
1877        Example:
1878            >>> Select().from_("tbl").select("x").sql()
1879            'SELECT x FROM tbl'
1880
1881        Args:
1882            *expressions (str | Expression): the SQL code strings to parse.
1883                If a `From` instance is passed, this is used as-is.
1884                If another `Expression` instance is passed, it will be wrapped in a `From`.
1885            append (bool): if `True`, add to any existing expressions.
1886                Otherwise, this flattens all the `From` expression into a single expression.
1887            dialect (str): the dialect used to parse the input expression.
1888            copy (bool): if `False`, modify this expression instance in-place.
1889            opts (kwargs): other options to use to parse the input expressions.
1890
1891        Returns:
1892            Select: the modified expression.
1893        """
1894        return _apply_child_list_builder(
1895            *expressions,
1896            instance=self,
1897            arg="from",
1898            append=append,
1899            copy=copy,
1900            prefix="FROM",
1901            into=From,
1902            dialect=dialect,
1903            **opts,
1904        )
1905
1906    def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1907        """
1908        Set the GROUP BY expression.
1909
1910        Example:
1911            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
1912            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
1913
1914        Args:
1915            *expressions (str | Expression): the SQL code strings to parse.
1916                If a `Group` instance is passed, this is used as-is.
1917                If another `Expression` instance is passed, it will be wrapped in a `Group`.
1918                If nothing is passed in then a group by is not applied to the expression
1919            append (bool): if `True`, add to any existing expressions.
1920                Otherwise, this flattens all the `Group` expression into a single expression.
1921            dialect (str): the dialect used to parse the input expression.
1922            copy (bool): if `False`, modify this expression instance in-place.
1923            opts (kwargs): other options to use to parse the input expressions.
1924
1925        Returns:
1926            Select: the modified expression.
1927        """
1928        if not expressions:
1929            return self if not copy else self.copy()
1930        return _apply_child_list_builder(
1931            *expressions,
1932            instance=self,
1933            arg="group",
1934            append=append,
1935            copy=copy,
1936            prefix="GROUP BY",
1937            into=Group,
1938            dialect=dialect,
1939            **opts,
1940        )
1941
1942    def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1943        """
1944        Set the ORDER BY expression.
1945
1946        Example:
1947            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
1948            'SELECT x FROM tbl ORDER BY x DESC'
1949
1950        Args:
1951            *expressions (str | Expression): the SQL code strings to parse.
1952                If a `Group` instance is passed, this is used as-is.
1953                If another `Expression` instance is passed, it will be wrapped in a `Order`.
1954            append (bool): if `True`, add to any existing expressions.
1955                Otherwise, this flattens all the `Order` expression into a single expression.
1956            dialect (str): the dialect used to parse the input expression.
1957            copy (bool): if `False`, modify this expression instance in-place.
1958            opts (kwargs): other options to use to parse the input expressions.
1959
1960        Returns:
1961            Select: the modified expression.
1962        """
1963        return _apply_child_list_builder(
1964            *expressions,
1965            instance=self,
1966            arg="order",
1967            append=append,
1968            copy=copy,
1969            prefix="ORDER BY",
1970            into=Order,
1971            dialect=dialect,
1972            **opts,
1973        )
1974
1975    def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1976        """
1977        Set the SORT BY expression.
1978
1979        Example:
1980            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql()
1981            'SELECT x FROM tbl SORT BY x DESC'
1982
1983        Args:
1984            *expressions (str | Expression): the SQL code strings to parse.
1985                If a `Group` instance is passed, this is used as-is.
1986                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
1987            append (bool): if `True`, add to any existing expressions.
1988                Otherwise, this flattens all the `Order` expression into a single expression.
1989            dialect (str): the dialect used to parse the input expression.
1990            copy (bool): if `False`, modify this expression instance in-place.
1991            opts (kwargs): other options to use to parse the input expressions.
1992
1993        Returns:
1994            Select: the modified expression.
1995        """
1996        return _apply_child_list_builder(
1997            *expressions,
1998            instance=self,
1999            arg="sort",
2000            append=append,
2001            copy=copy,
2002            prefix="SORT BY",
2003            into=Sort,
2004            dialect=dialect,
2005            **opts,
2006        )
2007
2008    def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2009        """
2010        Set the CLUSTER BY expression.
2011
2012        Example:
2013            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql()
2014            'SELECT x FROM tbl CLUSTER BY x DESC'
2015
2016        Args:
2017            *expressions (str | Expression): the SQL code strings to parse.
2018                If a `Group` instance is passed, this is used as-is.
2019                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2020            append (bool): if `True`, add to any existing expressions.
2021                Otherwise, this flattens all the `Order` expression into a single expression.
2022            dialect (str): the dialect used to parse the input expression.
2023            copy (bool): if `False`, modify this expression instance in-place.
2024            opts (kwargs): other options to use to parse the input expressions.
2025
2026        Returns:
2027            Select: the modified expression.
2028        """
2029        return _apply_child_list_builder(
2030            *expressions,
2031            instance=self,
2032            arg="cluster",
2033            append=append,
2034            copy=copy,
2035            prefix="CLUSTER BY",
2036            into=Cluster,
2037            dialect=dialect,
2038            **opts,
2039        )
2040
2041    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
2042        """
2043        Set the LIMIT expression.
2044
2045        Example:
2046            >>> Select().from_("tbl").select("x").limit(10).sql()
2047            'SELECT x FROM tbl LIMIT 10'
2048
2049        Args:
2050            expression (str | int | Expression): the SQL code string to parse.
2051                This can also be an integer.
2052                If a `Limit` instance is passed, this is used as-is.
2053                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2054            dialect (str): the dialect used to parse the input expression.
2055            copy (bool): if `False`, modify this expression instance in-place.
2056            opts (kwargs): other options to use to parse the input expressions.
2057
2058        Returns:
2059            Select: the modified expression.
2060        """
2061        return _apply_builder(
2062            expression=expression,
2063            instance=self,
2064            arg="limit",
2065            into=Limit,
2066            prefix="LIMIT",
2067            dialect=dialect,
2068            copy=copy,
2069            **opts,
2070        )
2071
2072    def offset(self, expression, dialect=None, copy=True, **opts) -> Select:
2073        """
2074        Set the OFFSET expression.
2075
2076        Example:
2077            >>> Select().from_("tbl").select("x").offset(10).sql()
2078            'SELECT x FROM tbl OFFSET 10'
2079
2080        Args:
2081            expression (str | int | Expression): the SQL code string to parse.
2082                This can also be an integer.
2083                If a `Offset` instance is passed, this is used as-is.
2084                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2085            dialect (str): the dialect used to parse the input expression.
2086            copy (bool): if `False`, modify this expression instance in-place.
2087            opts (kwargs): other options to use to parse the input expressions.
2088
2089        Returns:
2090            Select: the modified expression.
2091        """
2092        return _apply_builder(
2093            expression=expression,
2094            instance=self,
2095            arg="offset",
2096            into=Offset,
2097            prefix="OFFSET",
2098            dialect=dialect,
2099            copy=copy,
2100            **opts,
2101        )
2102
2103    def select(
2104        self,
2105        *expressions: str | Expression,
2106        append: bool = True,
2107        dialect: DialectType = None,
2108        copy: bool = True,
2109        **opts,
2110    ) -> Select:
2111        """
2112        Append to or set the SELECT expressions.
2113
2114        Example:
2115            >>> Select().select("x", "y").sql()
2116            'SELECT x, y'
2117
2118        Args:
2119            *expressions: the SQL code strings to parse.
2120                If an `Expression` instance is passed, it will be used as-is.
2121            append: if `True`, add to any existing expressions.
2122                Otherwise, this resets the expressions.
2123            dialect: the dialect used to parse the input expressions.
2124            copy: if `False`, modify this expression instance in-place.
2125            opts: other options to use to parse the input expressions.
2126
2127        Returns:
2128            Select: the modified expression.
2129        """
2130        return _apply_list_builder(
2131            *expressions,
2132            instance=self,
2133            arg="expressions",
2134            append=append,
2135            dialect=dialect,
2136            copy=copy,
2137            **opts,
2138        )
2139
2140    def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2141        """
2142        Append to or set the LATERAL expressions.
2143
2144        Example:
2145            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2146            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2147
2148        Args:
2149            *expressions (str | Expression): the SQL code strings to parse.
2150                If an `Expression` instance is passed, it will be used as-is.
2151            append (bool): if `True`, add to any existing expressions.
2152                Otherwise, this resets the expressions.
2153            dialect (str): the dialect used to parse the input expressions.
2154            copy (bool): if `False`, modify this expression instance in-place.
2155            opts (kwargs): other options to use to parse the input expressions.
2156
2157        Returns:
2158            Select: the modified expression.
2159        """
2160        return _apply_list_builder(
2161            *expressions,
2162            instance=self,
2163            arg="laterals",
2164            append=append,
2165            into=Lateral,
2166            prefix="LATERAL VIEW",
2167            dialect=dialect,
2168            copy=copy,
2169            **opts,
2170        )
2171
2172    def join(
2173        self,
2174        expression,
2175        on=None,
2176        using=None,
2177        append=True,
2178        join_type=None,
2179        join_alias=None,
2180        dialect=None,
2181        copy=True,
2182        **opts,
2183    ) -> Select:
2184        """
2185        Append to or set the JOIN expressions.
2186
2187        Example:
2188            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2189            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2190
2191            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2192            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2193
2194            Use `join_type` to change the type of join:
2195
2196            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2197            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2198
2199        Args:
2200            expression (str | Expression): the SQL code string to parse.
2201                If an `Expression` instance is passed, it will be used as-is.
2202            on (str | Expression): optionally specify the join "on" criteria as a SQL string.
2203                If an `Expression` instance is passed, it will be used as-is.
2204            using (str | Expression): optionally specify the join "using" criteria as a SQL string.
2205                If an `Expression` instance is passed, it will be used as-is.
2206            append (bool): if `True`, add to any existing expressions.
2207                Otherwise, this resets the expressions.
2208            join_type (str): If set, alter the parsed join type
2209            dialect (str): the dialect used to parse the input expressions.
2210            copy (bool): if `False`, modify this expression instance in-place.
2211            opts (kwargs): other options to use to parse the input expressions.
2212
2213        Returns:
2214            Select: the modified expression.
2215        """
2216        parse_args = {"dialect": dialect, **opts}
2217
2218        try:
2219            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
2220        except ParseError:
2221            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
2222
2223        join = expression if isinstance(expression, Join) else Join(this=expression)
2224
2225        if isinstance(join.this, Select):
2226            join.this.replace(join.this.subquery())
2227
2228        if join_type:
2229            natural: t.Optional[Token]
2230            side: t.Optional[Token]
2231            kind: t.Optional[Token]
2232
2233            natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
2234
2235            if natural:
2236                join.set("natural", True)
2237            if side:
2238                join.set("side", side.text)
2239            if kind:
2240                join.set("kind", kind.text)
2241
2242        if on:
2243            on = and_(*ensure_collection(on), dialect=dialect, **opts)
2244            join.set("on", on)
2245
2246        if using:
2247            join = _apply_list_builder(
2248                *ensure_collection(using),
2249                instance=join,
2250                arg="using",
2251                append=append,
2252                copy=copy,
2253                **opts,
2254            )
2255
2256        if join_alias:
2257            join.set("this", alias_(join.this, join_alias, table=True))
2258        return _apply_list_builder(
2259            join,
2260            instance=self,
2261            arg="joins",
2262            append=append,
2263            copy=copy,
2264            **opts,
2265        )
2266
2267    def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2268        """
2269        Append to or set the WHERE expressions.
2270
2271        Example:
2272            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
2273            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
2274
2275        Args:
2276            *expressions (str | Expression): the SQL code strings to parse.
2277                If an `Expression` instance is passed, it will be used as-is.
2278                Multiple expressions are combined with an AND operator.
2279            append (bool): if `True`, AND the new expressions to any existing expression.
2280                Otherwise, this resets the expression.
2281            dialect (str): the dialect used to parse the input expressions.
2282            copy (bool): if `False`, modify this expression instance in-place.
2283            opts (kwargs): other options to use to parse the input expressions.
2284
2285        Returns:
2286            Select: the modified expression.
2287        """
2288        return _apply_conjunction_builder(
2289            *expressions,
2290            instance=self,
2291            arg="where",
2292            append=append,
2293            into=Where,
2294            dialect=dialect,
2295            copy=copy,
2296            **opts,
2297        )
2298
2299    def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2300        """
2301        Append to or set the HAVING expressions.
2302
2303        Example:
2304            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
2305            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
2306
2307        Args:
2308            *expressions (str | Expression): the SQL code strings to parse.
2309                If an `Expression` instance is passed, it will be used as-is.
2310                Multiple expressions are combined with an AND operator.
2311            append (bool): if `True`, AND the new expressions to any existing expression.
2312                Otherwise, this resets the expression.
2313            dialect (str): the dialect used to parse the input expressions.
2314            copy (bool): if `False`, modify this expression instance in-place.
2315            opts (kwargs): other options to use to parse the input expressions.
2316
2317        Returns:
2318            Select: the modified expression.
2319        """
2320        return _apply_conjunction_builder(
2321            *expressions,
2322            instance=self,
2323            arg="having",
2324            append=append,
2325            into=Having,
2326            dialect=dialect,
2327            copy=copy,
2328            **opts,
2329        )
2330
2331    def window(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2332        return _apply_list_builder(
2333            *expressions,
2334            instance=self,
2335            arg="windows",
2336            append=append,
2337            into=Window,
2338            dialect=dialect,
2339            copy=copy,
2340            **opts,
2341        )
2342
2343    def distinct(self, distinct=True, copy=True) -> Select:
2344        """
2345        Set the OFFSET expression.
2346
2347        Example:
2348            >>> Select().from_("tbl").select("x").distinct().sql()
2349            'SELECT DISTINCT x FROM tbl'
2350
2351        Args:
2352            distinct (bool): whether the Select should be distinct
2353            copy (bool): if `False`, modify this expression instance in-place.
2354
2355        Returns:
2356            Select: the modified expression.
2357        """
2358        instance = _maybe_copy(self, copy)
2359        instance.set("distinct", Distinct() if distinct else None)
2360        return instance
2361
2362    def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create:
2363        """
2364        Convert this expression to a CREATE TABLE AS statement.
2365
2366        Example:
2367            >>> Select().select("*").from_("tbl").ctas("x").sql()
2368            'CREATE TABLE x AS SELECT * FROM tbl'
2369
2370        Args:
2371            table (str | Expression): the SQL code string to parse as the table name.
2372                If another `Expression` instance is passed, it will be used as-is.
2373            properties (dict): an optional mapping of table properties
2374            dialect (str): the dialect used to parse the input table.
2375            copy (bool): if `False`, modify this expression instance in-place.
2376            opts (kwargs): other options to use to parse the input table.
2377
2378        Returns:
2379            Create: the CREATE TABLE AS expression
2380        """
2381        instance = _maybe_copy(self, copy)
2382        table_expression = maybe_parse(
2383            table,
2384            into=Table,
2385            dialect=dialect,
2386            **opts,
2387        )
2388        properties_expression = None
2389        if properties:
2390            properties_expression = Properties.from_dict(properties)
2391
2392        return Create(
2393            this=table_expression,
2394            kind="table",
2395            expression=instance,
2396            properties=properties_expression,
2397        )
2398
2399    def lock(self, update: bool = True, copy: bool = True) -> Select:
2400        """
2401        Set the locking read mode for this expression.
2402
2403        Examples:
2404            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
2405            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
2406
2407            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
2408            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
2409
2410        Args:
2411            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
2412            copy: if `False`, modify this expression instance in-place.
2413
2414        Returns:
2415            The modified expression.
2416        """
2417
2418        inst = _maybe_copy(self, copy)
2419        inst.set("lock", Lock(update=update))
2420
2421        return inst
2422
2423    @property
2424    def named_selects(self) -> t.List[str]:
2425        return [e.output_name for e in self.expressions if e.alias_or_name]
2426
2427    @property
2428    def selects(self) -> t.List[Expression]:
2429        return self.expressions
2430
2431
2432class Subquery(DerivedTable, Unionable):
2433    arg_types = {
2434        "this": True,
2435        "alias": False,
2436        "with": False,
2437        **QUERY_MODIFIERS,
2438    }
2439
2440    def unnest(self):
2441        """
2442        Returns the first non subquery.
2443        """
2444        expression = self
2445        while isinstance(expression, Subquery):
2446            expression = expression.this
2447        return expression
2448
2449    @property
2450    def output_name(self):
2451        return self.alias
2452
2453
2454class TableSample(Expression):
2455    arg_types = {
2456        "this": False,
2457        "method": False,
2458        "bucket_numerator": False,
2459        "bucket_denominator": False,
2460        "bucket_field": False,
2461        "percent": False,
2462        "rows": False,
2463        "size": False,
2464        "seed": False,
2465    }
2466
2467
2468class Tag(Expression):
2469    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
2470
2471    arg_types = {
2472        "this": False,
2473        "prefix": False,
2474        "postfix": False,
2475    }
2476
2477
2478class Pivot(Expression):
2479    arg_types = {
2480        "this": False,
2481        "expressions": True,
2482        "field": True,
2483        "unpivot": True,
2484    }
2485
2486
2487class Window(Expression):
2488    arg_types = {
2489        "this": True,
2490        "partition_by": False,
2491        "order": False,
2492        "spec": False,
2493        "alias": False,
2494    }
2495
2496
2497class WindowSpec(Expression):
2498    arg_types = {
2499        "kind": False,
2500        "start": False,
2501        "start_side": False,
2502        "end": False,
2503        "end_side": False,
2504    }
2505
2506
2507class Where(Expression):
2508    pass
2509
2510
2511class Star(Expression):
2512    arg_types = {"except": False, "replace": False}
2513
2514    @property
2515    def name(self) -> str:
2516        return "*"
2517
2518    @property
2519    def output_name(self):
2520        return self.name
2521
2522
2523class Parameter(Expression):
2524    arg_types = {"this": True, "wrapped": False}
2525
2526
2527class SessionParameter(Expression):
2528    arg_types = {"this": True, "kind": False}
2529
2530
2531class Placeholder(Expression):
2532    arg_types = {"this": False}
2533
2534
2535class Null(Condition):
2536    arg_types: t.Dict[str, t.Any] = {}
2537
2538    @property
2539    def name(self) -> str:
2540        return "NULL"
2541
2542
2543class Boolean(Condition):
2544    pass
2545
2546
2547class DataType(Expression):
2548    arg_types = {
2549        "this": True,
2550        "expressions": False,
2551        "nested": False,
2552        "values": False,
2553        "prefix": False,
2554    }
2555
2556    class Type(AutoName):
2557        CHAR = auto()
2558        NCHAR = auto()
2559        VARCHAR = auto()
2560        NVARCHAR = auto()
2561        TEXT = auto()
2562        MEDIUMTEXT = auto()
2563        LONGTEXT = auto()
2564        MEDIUMBLOB = auto()
2565        LONGBLOB = auto()
2566        BINARY = auto()
2567        VARBINARY = auto()
2568        INT = auto()
2569        TINYINT = auto()
2570        SMALLINT = auto()
2571        BIGINT = auto()
2572        FLOAT = auto()
2573        DOUBLE = auto()
2574        DECIMAL = auto()
2575        BOOLEAN = auto()
2576        JSON = auto()
2577        JSONB = auto()
2578        INTERVAL = auto()
2579        TIME = auto()
2580        TIMESTAMP = auto()
2581        TIMESTAMPTZ = auto()
2582        TIMESTAMPLTZ = auto()
2583        DATE = auto()
2584        DATETIME = auto()
2585        ARRAY = auto()
2586        MAP = auto()
2587        UUID = auto()
2588        GEOGRAPHY = auto()
2589        GEOMETRY = auto()
2590        STRUCT = auto()
2591        NULLABLE = auto()
2592        HLLSKETCH = auto()
2593        HSTORE = auto()
2594        SUPER = auto()
2595        SERIAL = auto()
2596        SMALLSERIAL = auto()
2597        BIGSERIAL = auto()
2598        XML = auto()
2599        UNIQUEIDENTIFIER = auto()
2600        MONEY = auto()
2601        SMALLMONEY = auto()
2602        ROWVERSION = auto()
2603        IMAGE = auto()
2604        VARIANT = auto()
2605        OBJECT = auto()
2606        NULL = auto()
2607        UNKNOWN = auto()  # Sentinel value, useful for type annotation
2608
2609    TEXT_TYPES = {
2610        Type.CHAR,
2611        Type.NCHAR,
2612        Type.VARCHAR,
2613        Type.NVARCHAR,
2614        Type.TEXT,
2615    }
2616
2617    INTEGER_TYPES = {
2618        Type.INT,
2619        Type.TINYINT,
2620        Type.SMALLINT,
2621        Type.BIGINT,
2622    }
2623
2624    FLOAT_TYPES = {
2625        Type.FLOAT,
2626        Type.DOUBLE,
2627    }
2628
2629    NUMERIC_TYPES = {*INTEGER_TYPES, *FLOAT_TYPES}
2630
2631    TEMPORAL_TYPES = {
2632        Type.TIMESTAMP,
2633        Type.TIMESTAMPTZ,
2634        Type.TIMESTAMPLTZ,
2635        Type.DATE,
2636        Type.DATETIME,
2637    }
2638
2639    @classmethod
2640    def build(
2641        cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs
2642    ) -> DataType:
2643        from sqlglot import parse_one
2644
2645        if isinstance(dtype, str):
2646            if dtype.upper() in cls.Type.__members__:
2647                data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()])
2648            else:
2649                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
2650            if data_type_exp is None:
2651                raise ValueError(f"Unparsable data type value: {dtype}")
2652        elif isinstance(dtype, DataType.Type):
2653            data_type_exp = DataType(this=dtype)
2654        elif isinstance(dtype, DataType):
2655            return dtype
2656        else:
2657            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
2658        return DataType(**{**data_type_exp.args, **kwargs})
2659
2660    def is_type(self, dtype: DataType.Type) -> bool:
2661        return self.this == dtype
2662
2663
2664# https://www.postgresql.org/docs/15/datatype-pseudo.html
2665class PseudoType(Expression):
2666    pass
2667
2668
2669class StructKwarg(Expression):
2670    arg_types = {"this": True, "expression": True}
2671
2672
2673# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
2674class SubqueryPredicate(Predicate):
2675    pass
2676
2677
2678class All(SubqueryPredicate):
2679    pass
2680
2681
2682class Any(SubqueryPredicate):
2683    pass
2684
2685
2686class Exists(SubqueryPredicate):
2687    pass
2688
2689
2690# Commands to interact with the databases or engines. For most of the command
2691# expressions we parse whatever comes after the command's name as a string.
2692class Command(Expression):
2693    arg_types = {"this": True, "expression": False}
2694
2695
2696class Transaction(Expression):
2697    arg_types = {"this": False, "modes": False}
2698
2699
2700class Commit(Expression):
2701    arg_types = {"chain": False}
2702
2703
2704class Rollback(Expression):
2705    arg_types = {"savepoint": False}
2706
2707
2708class AlterTable(Expression):
2709    arg_types = {"this": True, "actions": True, "exists": False}
2710
2711
2712class AddConstraint(Expression):
2713    arg_types = {"this": False, "expression": False, "enforced": False}
2714
2715
2716class DropPartition(Expression):
2717    arg_types = {"expressions": True, "exists": False}
2718
2719
2720# Binary expressions like (ADD a b)
2721class Binary(Expression):
2722    arg_types = {"this": True, "expression": True}
2723
2724    @property
2725    def left(self):
2726        return self.this
2727
2728    @property
2729    def right(self):
2730        return self.expression
2731
2732
2733class Add(Binary):
2734    pass
2735
2736
2737class Connector(Binary, Condition):
2738    pass
2739
2740
2741class And(Connector):
2742    pass
2743
2744
2745class Or(Connector):
2746    pass
2747
2748
2749class BitwiseAnd(Binary):
2750    pass
2751
2752
2753class BitwiseLeftShift(Binary):
2754    pass
2755
2756
2757class BitwiseOr(Binary):
2758    pass
2759
2760
2761class BitwiseRightShift(Binary):
2762    pass
2763
2764
2765class BitwiseXor(Binary):
2766    pass
2767
2768
2769class Div(Binary):
2770    pass
2771
2772
2773class Dot(Binary):
2774    @property
2775    def name(self) -> str:
2776        return self.expression.name
2777
2778
2779class DPipe(Binary):
2780    pass
2781
2782
2783class EQ(Binary, Predicate):
2784    pass
2785
2786
2787class NullSafeEQ(Binary, Predicate):
2788    pass
2789
2790
2791class NullSafeNEQ(Binary, Predicate):
2792    pass
2793
2794
2795class Distance(Binary):
2796    pass
2797
2798
2799class Escape(Binary):
2800    pass
2801
2802
2803class Glob(Binary, Predicate):
2804    pass
2805
2806
2807class GT(Binary, Predicate):
2808    pass
2809
2810
2811class GTE(Binary, Predicate):
2812    pass
2813
2814
2815class ILike(Binary, Predicate):
2816    pass
2817
2818
2819class ILikeAny(Binary, Predicate):
2820    pass
2821
2822
2823class IntDiv(Binary):
2824    pass
2825
2826
2827class Is(Binary, Predicate):
2828    pass
2829
2830
2831class Kwarg(Binary):
2832    """Kwarg in special functions like func(kwarg => y)."""
2833
2834
2835class Like(Binary, Predicate):
2836    pass
2837
2838
2839class LikeAny(Binary, Predicate):
2840    pass
2841
2842
2843class LT(Binary, Predicate):
2844    pass
2845
2846
2847class LTE(Binary, Predicate):
2848    pass
2849
2850
2851class Mod(Binary):
2852    pass
2853
2854
2855class Mul(Binary):
2856    pass
2857
2858
2859class NEQ(Binary, Predicate):
2860    pass
2861
2862
2863class SimilarTo(Binary, Predicate):
2864    pass
2865
2866
2867class Slice(Binary):
2868    arg_types = {"this": False, "expression": False}
2869
2870
2871class Sub(Binary):
2872    pass
2873
2874
2875# Unary Expressions
2876# (NOT a)
2877class Unary(Expression):
2878    pass
2879
2880
2881class BitwiseNot(Unary):
2882    pass
2883
2884
2885class Not(Unary, Condition):
2886    pass
2887
2888
2889class Paren(Unary, Condition):
2890    arg_types = {"this": True, "with": False}
2891
2892
2893class Neg(Unary):
2894    pass
2895
2896
2897# Special Functions
2898class Alias(Expression):
2899    arg_types = {"this": True, "alias": False}
2900
2901    @property
2902    def output_name(self):
2903        return self.alias
2904
2905
2906class Aliases(Expression):
2907    arg_types = {"this": True, "expressions": True}
2908
2909    @property
2910    def aliases(self):
2911        return self.expressions
2912
2913
2914class AtTimeZone(Expression):
2915    arg_types = {"this": True, "zone": True}
2916
2917
2918class Between(Predicate):
2919    arg_types = {"this": True, "low": True, "high": True}
2920
2921
2922class Bracket(Condition):
2923    arg_types = {"this": True, "expressions": True}
2924
2925
2926class Distinct(Expression):
2927    arg_types = {"expressions": False, "on": False}
2928
2929
2930class In(Predicate):
2931    arg_types = {
2932        "this": True,
2933        "expressions": False,
2934        "query": False,
2935        "unnest": False,
2936        "field": False,
2937        "is_global": False,
2938    }
2939
2940
2941class TimeUnit(Expression):
2942    """Automatically converts unit arg into a var."""
2943
2944    arg_types = {"unit": False}
2945
2946    def __init__(self, **args):
2947        unit = args.get("unit")
2948        if isinstance(unit, Column):
2949            args["unit"] = Var(this=unit.name)
2950        elif isinstance(unit, Week):
2951            unit.set("this", Var(this=unit.this.name))
2952        super().__init__(**args)
2953
2954
2955class Interval(TimeUnit):
2956    arg_types = {"this": False, "unit": False}
2957
2958
2959class IgnoreNulls(Expression):
2960    pass
2961
2962
2963class RespectNulls(Expression):
2964    pass
2965
2966
2967# Functions
2968class Func(Condition):
2969    """
2970    The base class for all function expressions.
2971
2972    Attributes:
2973        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
2974            treated as a variable length argument and the argument's value will be stored as a list.
2975        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
2976            for this function expression. These values are used to map this node to a name during parsing
2977            as well as to provide the function's name during SQL string generation. By default the SQL
2978            name is set to the expression's class name transformed to snake case.
2979    """
2980
2981    is_var_len_args = False
2982
2983    @classmethod
2984    def from_arg_list(cls, args):
2985        if cls.is_var_len_args:
2986            all_arg_keys = list(cls.arg_types)
2987            # If this function supports variable length argument treat the last argument as such.
2988            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
2989            num_non_var = len(non_var_len_arg_keys)
2990
2991            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
2992            args_dict[all_arg_keys[-1]] = args[num_non_var:]
2993        else:
2994            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
2995
2996        return cls(**args_dict)
2997
2998    @classmethod
2999    def sql_names(cls):
3000        if cls is Func:
3001            raise NotImplementedError(
3002                "SQL name is only supported by concrete function implementations"
3003            )
3004        if "_sql_names" not in cls.__dict__:
3005            cls._sql_names = [camel_to_snake_case(cls.__name__)]
3006        return cls._sql_names
3007
3008    @classmethod
3009    def sql_name(cls):
3010        return cls.sql_names()[0]
3011
3012    @classmethod
3013    def default_parser_mappings(cls):
3014        return {name: cls.from_arg_list for name in cls.sql_names()}
3015
3016
3017class AggFunc(Func):
3018    pass
3019
3020
3021class Abs(Func):
3022    pass
3023
3024
3025class Anonymous(Func):
3026    arg_types = {"this": True, "expressions": False}
3027    is_var_len_args = True
3028
3029
3030class ApproxDistinct(AggFunc):
3031    arg_types = {"this": True, "accuracy": False}
3032
3033
3034class Array(Func):
3035    arg_types = {"expressions": False}
3036    is_var_len_args = True
3037
3038
3039class GenerateSeries(Func):
3040    arg_types = {"start": True, "end": True, "step": False}
3041
3042
3043class ArrayAgg(AggFunc):
3044    pass
3045
3046
3047class ArrayAll(Func):
3048    arg_types = {"this": True, "expression": True}
3049
3050
3051class ArrayAny(Func):
3052    arg_types = {"this": True, "expression": True}
3053
3054
3055class ArrayConcat(Func):
3056    arg_types = {"this": True, "expressions": False}
3057    is_var_len_args = True
3058
3059
3060class ArrayContains(Func):
3061    arg_types = {"this": True, "expression": True}
3062
3063
3064class ArrayFilter(Func):
3065    arg_types = {"this": True, "expression": True}
3066    _sql_names = ["FILTER", "ARRAY_FILTER"]
3067
3068
3069class ArraySize(Func):
3070    arg_types = {"this": True, "expression": False}
3071
3072
3073class ArraySort(Func):
3074    arg_types = {"this": True, "expression": False}
3075
3076
3077class ArraySum(Func):
3078    pass
3079
3080
3081class ArrayUnionAgg(AggFunc):
3082    pass
3083
3084
3085class Avg(AggFunc):
3086    pass
3087
3088
3089class AnyValue(AggFunc):
3090    pass
3091
3092
3093class Case(Func):
3094    arg_types = {"this": False, "ifs": True, "default": False}
3095
3096
3097class Cast(Func):
3098    arg_types = {"this": True, "to": True}
3099
3100    @property
3101    def name(self) -> str:
3102        return self.this.name
3103
3104    @property
3105    def to(self):
3106        return self.args["to"]
3107
3108    @property
3109    def output_name(self):
3110        return self.name
3111
3112    def is_type(self, dtype: DataType.Type) -> bool:
3113        return self.to.is_type(dtype)
3114
3115
3116class Collate(Binary):
3117    pass
3118
3119
3120class TryCast(Cast):
3121    pass
3122
3123
3124class Ceil(Func):
3125    arg_types = {"this": True, "decimals": False}
3126    _sql_names = ["CEIL", "CEILING"]
3127
3128
3129class Coalesce(Func):
3130    arg_types = {"this": True, "expressions": False}
3131    is_var_len_args = True
3132
3133
3134class Concat(Func):
3135    arg_types = {"expressions": True}
3136    is_var_len_args = True
3137
3138
3139class ConcatWs(Concat):
3140    _sql_names = ["CONCAT_WS"]
3141
3142
3143class Count(AggFunc):
3144    arg_types = {"this": False}
3145
3146
3147class CurrentDate(Func):
3148    arg_types = {"this": False}
3149
3150
3151class CurrentDatetime(Func):
3152    arg_types = {"this": False}
3153
3154
3155class CurrentTime(Func):
3156    arg_types = {"this": False}
3157
3158
3159class CurrentTimestamp(Func):
3160    arg_types = {"this": False}
3161
3162
3163class DateAdd(Func, TimeUnit):
3164    arg_types = {"this": True, "expression": True, "unit": False}
3165
3166
3167class DateSub(Func, TimeUnit):
3168    arg_types = {"this": True, "expression": True, "unit": False}
3169
3170
3171class DateDiff(Func, TimeUnit):
3172    arg_types = {"this": True, "expression": True, "unit": False}
3173
3174
3175class DateTrunc(Func):
3176    arg_types = {"unit": True, "this": True, "zone": False}
3177
3178
3179class DatetimeAdd(Func, TimeUnit):
3180    arg_types = {"this": True, "expression": True, "unit": False}
3181
3182
3183class DatetimeSub(Func, TimeUnit):
3184    arg_types = {"this": True, "expression": True, "unit": False}
3185
3186
3187class DatetimeDiff(Func, TimeUnit):
3188    arg_types = {"this": True, "expression": True, "unit": False}
3189
3190
3191class DatetimeTrunc(Func, TimeUnit):
3192    arg_types = {"this": True, "unit": True, "zone": False}
3193
3194
3195class DayOfWeek(Func):
3196    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
3197
3198
3199class DayOfMonth(Func):
3200    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
3201
3202
3203class DayOfYear(Func):
3204    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
3205
3206
3207class WeekOfYear(Func):
3208    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
3209
3210
3211class LastDateOfMonth(Func):
3212    pass
3213
3214
3215class Extract(Func):
3216    arg_types = {"this": True, "expression": True}
3217
3218
3219class TimestampAdd(Func, TimeUnit):
3220    arg_types = {"this": True, "expression": True, "unit": False}
3221
3222
3223class TimestampSub(Func, TimeUnit):
3224    arg_types = {"this": True, "expression": True, "unit": False}
3225
3226
3227class TimestampDiff(Func, TimeUnit):
3228    arg_types = {"this": True, "expression": True, "unit": False}
3229
3230
3231class TimestampTrunc(Func, TimeUnit):
3232    arg_types = {"this": True, "unit": True, "zone": False}
3233
3234
3235class TimeAdd(Func, TimeUnit):
3236    arg_types = {"this": True, "expression": True, "unit": False}
3237
3238
3239class TimeSub(Func, TimeUnit):
3240    arg_types = {"this": True, "expression": True, "unit": False}
3241
3242
3243class TimeDiff(Func, TimeUnit):
3244    arg_types = {"this": True, "expression": True, "unit": False}
3245
3246
3247class TimeTrunc(Func, TimeUnit):
3248    arg_types = {"this": True, "unit": True, "zone": False}
3249
3250
3251class DateFromParts(Func):
3252    _sql_names = ["DATEFROMPARTS"]
3253    arg_types = {"year": True, "month": True, "day": True}
3254
3255
3256class DateStrToDate(Func):
3257    pass
3258
3259
3260class DateToDateStr(Func):
3261    pass
3262
3263
3264class DateToDi(Func):
3265    pass
3266
3267
3268class Day(Func):
3269    pass
3270
3271
3272class Decode(Func):
3273    arg_types = {"this": True, "charset": True, "replace": False}
3274
3275
3276class DiToDate(Func):
3277    pass
3278
3279
3280class Encode(Func):
3281    arg_types = {"this": True, "charset": True}
3282
3283
3284class Exp(Func):
3285    pass
3286
3287
3288class Explode(Func):
3289    pass
3290
3291
3292class Floor(Func):
3293    arg_types = {"this": True, "decimals": False}
3294
3295
3296class Greatest(Func):
3297    arg_types = {"this": True, "expressions": False}
3298    is_var_len_args = True
3299
3300
3301class GroupConcat(Func):
3302    arg_types = {"this": True, "separator": False}
3303
3304
3305class Hex(Func):
3306    pass
3307
3308
3309class If(Func):
3310    arg_types = {"this": True, "true": True, "false": False}
3311
3312
3313class IfNull(Func):
3314    arg_types = {"this": True, "expression": False}
3315    _sql_names = ["IFNULL", "NVL"]
3316
3317
3318class Initcap(Func):
3319    pass
3320
3321
3322class JSONBContains(Binary):
3323    _sql_names = ["JSONB_CONTAINS"]
3324
3325
3326class JSONExtract(Binary, Func):
3327    _sql_names = ["JSON_EXTRACT"]
3328
3329
3330class JSONExtractScalar(JSONExtract):
3331    _sql_names = ["JSON_EXTRACT_SCALAR"]
3332
3333
3334class JSONBExtract(JSONExtract):
3335    _sql_names = ["JSONB_EXTRACT"]
3336
3337
3338class JSONBExtractScalar(JSONExtract):
3339    _sql_names = ["JSONB_EXTRACT_SCALAR"]
3340
3341
3342class Least(Func):
3343    arg_types = {"this": True, "expressions": False}
3344    is_var_len_args = True
3345
3346
3347class Length(Func):
3348    pass
3349
3350
3351class Levenshtein(Func):
3352    arg_types = {
3353        "this": True,
3354        "expression": False,
3355        "ins_cost": False,
3356        "del_cost": False,
3357        "sub_cost": False,
3358    }
3359
3360
3361class Ln(Func):
3362    pass
3363
3364
3365class Log(Func):
3366    arg_types = {"this": True, "expression": False}
3367
3368
3369class Log2(Func):
3370    pass
3371
3372
3373class Log10(Func):
3374    pass
3375
3376
3377class LogicalOr(AggFunc):
3378    _sql_names = ["LOGICAL_OR", "BOOL_OR"]
3379
3380
3381class Lower(Func):
3382    _sql_names = ["LOWER", "LCASE"]
3383
3384
3385class Map(Func):
3386    arg_types = {"keys": False, "values": False}
3387
3388
3389class VarMap(Func):
3390    arg_types = {"keys": True, "values": True}
3391    is_var_len_args = True
3392
3393
3394class Matches(Func):
3395    """Oracle/Snowflake decode.
3396    https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm
3397    Pattern matching MATCHES(value, search1, result1, ...searchN, resultN, else)
3398    """
3399
3400    arg_types = {"this": True, "expressions": True}
3401    is_var_len_args = True
3402
3403
3404class Max(AggFunc):
3405    arg_types = {"this": True, "expression": False}
3406
3407
3408class Min(AggFunc):
3409    arg_types = {"this": True, "expression": False}
3410
3411
3412class Month(Func):
3413    pass
3414
3415
3416class Nvl2(Func):
3417    arg_types = {"this": True, "true": True, "false": False}
3418
3419
3420class Posexplode(Func):
3421    pass
3422
3423
3424class Pow(Binary, Func):
3425    _sql_names = ["POWER", "POW"]
3426
3427
3428class PercentileCont(AggFunc):
3429    pass
3430
3431
3432class PercentileDisc(AggFunc):
3433    pass
3434
3435
3436class Quantile(AggFunc):
3437    arg_types = {"this": True, "quantile": True}
3438
3439
3440# Clickhouse-specific:
3441# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantiles/#quantiles
3442class Quantiles(AggFunc):
3443    arg_types = {"parameters": True, "expressions": True}
3444
3445
3446class QuantileIf(AggFunc):
3447    arg_types = {"parameters": True, "expressions": True}
3448
3449
3450class ApproxQuantile(Quantile):
3451    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
3452
3453
3454class ReadCSV(Func):
3455    _sql_names = ["READ_CSV"]
3456    is_var_len_args = True
3457    arg_types = {"this": True, "expressions": False}
3458
3459
3460class Reduce(Func):
3461    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
3462
3463
3464class RegexpExtract(Func):
3465    arg_types = {
3466        "this": True,
3467        "expression": True,
3468        "position": False,
3469        "occurrence": False,
3470        "group": False,
3471    }
3472
3473
3474class RegexpLike(Func):
3475    arg_types = {"this": True, "expression": True, "flag": False}
3476
3477
3478class RegexpILike(Func):
3479    arg_types = {"this": True, "expression": True, "flag": False}
3480
3481
3482class RegexpSplit(Func):
3483    arg_types = {"this": True, "expression": True}
3484
3485
3486class Repeat(Func):
3487    arg_types = {"this": True, "times": True}
3488
3489
3490class Round(Func):
3491    arg_types = {"this": True, "decimals": False}
3492
3493
3494class RowNumber(Func):
3495    arg_types: t.Dict[str, t.Any] = {}
3496
3497
3498class SafeDivide(Func):
3499    arg_types = {"this": True, "expression": True}
3500
3501
3502class SetAgg(AggFunc):
3503    pass
3504
3505
3506class SortArray(Func):
3507    arg_types = {"this": True, "asc": False}
3508
3509
3510class Split(Func):
3511    arg_types = {"this": True, "expression": True, "limit": False}
3512
3513
3514# Start may be omitted in the case of postgres
3515# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
3516class Substring(Func):
3517    arg_types = {"this": True, "start": False, "length": False}
3518
3519
3520class StrPosition(Func):
3521    arg_types = {
3522        "this": True,
3523        "substr": True,
3524        "position": False,
3525        "instance": False,
3526    }
3527
3528
3529class StrToDate(Func):
3530    arg_types = {"this": True, "format": True}
3531
3532
3533class StrToTime(Func):
3534    arg_types = {"this": True, "format": True}
3535
3536
3537# Spark allows unix_timestamp()
3538# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
3539class StrToUnix(Func):
3540    arg_types = {"this": False, "format": False}
3541
3542
3543class NumberToStr(Func):
3544    arg_types = {"this": True, "format": True}
3545
3546
3547class Struct(Func):
3548    arg_types = {"expressions": True}
3549    is_var_len_args = True
3550
3551
3552class StructExtract(Func):
3553    arg_types = {"this": True, "expression": True}
3554
3555
3556class Sum(AggFunc):
3557    pass
3558
3559
3560class Sqrt(Func):
3561    pass
3562
3563
3564class Stddev(AggFunc):
3565    pass
3566
3567
3568class StddevPop(AggFunc):
3569    pass
3570
3571
3572class StddevSamp(AggFunc):
3573    pass
3574
3575
3576class TimeToStr(Func):
3577    arg_types = {"this": True, "format": True}
3578
3579
3580class TimeToTimeStr(Func):
3581    pass
3582
3583
3584class TimeToUnix(Func):
3585    pass
3586
3587
3588class TimeStrToDate(Func):
3589    pass
3590
3591
3592class TimeStrToTime(Func):
3593    pass
3594
3595
3596class TimeStrToUnix(Func):
3597    pass
3598
3599
3600class Trim(Func):
3601    arg_types = {
3602        "this": True,
3603        "expression": False,
3604        "position": False,
3605        "collation": False,
3606    }
3607
3608
3609class TsOrDsAdd(Func, TimeUnit):
3610    arg_types = {"this": True, "expression": True, "unit": False}
3611
3612
3613class TsOrDsToDateStr(Func):
3614    pass
3615
3616
3617class TsOrDsToDate(Func):
3618    arg_types = {"this": True, "format": False}
3619
3620
3621class TsOrDiToDi(Func):
3622    pass
3623
3624
3625class Unhex(Func):
3626    pass
3627
3628
3629class UnixToStr(Func):
3630    arg_types = {"this": True, "format": False}
3631
3632
3633# https://prestodb.io/docs/current/functions/datetime.html
3634# presto has weird zone/hours/minutes
3635class UnixToTime(Func):
3636    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
3637
3638    SECONDS = Literal.string("seconds")
3639    MILLIS = Literal.string("millis")
3640    MICROS = Literal.string("micros")
3641
3642
3643class UnixToTimeStr(Func):
3644    pass
3645
3646
3647class Upper(Func):
3648    _sql_names = ["UPPER", "UCASE"]
3649
3650
3651class Variance(AggFunc):
3652    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
3653
3654
3655class VariancePop(AggFunc):
3656    _sql_names = ["VARIANCE_POP", "VAR_POP"]
3657
3658
3659class Week(Func):
3660    arg_types = {"this": True, "mode": False}
3661
3662
3663class XMLTable(Func):
3664    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
3665
3666
3667class Year(Func):
3668    pass
3669
3670
3671class Use(Expression):
3672    arg_types = {"this": True, "kind": False}
3673
3674
3675class Merge(Expression):
3676    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
3677
3678
3679class When(Func):
3680    arg_types = {"this": True, "then": True}
3681
3682
3683def _norm_args(expression):
3684    args = {}
3685
3686    for k, arg in expression.args.items():
3687        if isinstance(arg, list):
3688            arg = [_norm_arg(a) for a in arg]
3689            if not arg:
3690                arg = None
3691        else:
3692            arg = _norm_arg(arg)
3693
3694        if arg is not None and arg is not False:
3695            args[k] = arg
3696
3697    return args
3698
3699
3700def _norm_arg(arg):
3701    return arg.lower() if isinstance(arg, str) else arg
3702
3703
3704ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
3705
3706
3707# Helpers
3708def maybe_parse(
3709    sql_or_expression: str | Expression,
3710    *,
3711    into: t.Optional[IntoType] = None,
3712    dialect: DialectType = None,
3713    prefix: t.Optional[str] = None,
3714    copy: bool = False,
3715    **opts,
3716) -> Expression:
3717    """Gracefully handle a possible string or expression.
3718
3719    Example:
3720        >>> maybe_parse("1")
3721        (LITERAL this: 1, is_string: False)
3722        >>> maybe_parse(to_identifier("x"))
3723        (IDENTIFIER this: x, quoted: False)
3724
3725    Args:
3726        sql_or_expression: the SQL code string or an expression
3727        into: the SQLGlot Expression to parse into
3728        dialect: the dialect used to parse the input expressions (in the case that an
3729            input expression is a SQL string).
3730        prefix: a string to prefix the sql with before it gets parsed
3731            (automatically includes a space)
3732        copy: whether or not to copy the expression.
3733        **opts: other options to use to parse the input expressions (again, in the case
3734            that an input expression is a SQL string).
3735
3736    Returns:
3737        Expression: the parsed or given expression.
3738    """
3739    if isinstance(sql_or_expression, Expression):
3740        if copy:
3741            return sql_or_expression.copy()
3742        return sql_or_expression
3743
3744    import sqlglot
3745
3746    sql = str(sql_or_expression)
3747    if prefix:
3748        sql = f"{prefix} {sql}"
3749    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
3750
3751
3752def _maybe_copy(instance, copy=True):
3753    return instance.copy() if copy else instance
3754
3755
3756def _is_wrong_expression(expression, into):
3757    return isinstance(expression, Expression) and not isinstance(expression, into)
3758
3759
3760def _apply_builder(
3761    expression,
3762    instance,
3763    arg,
3764    copy=True,
3765    prefix=None,
3766    into=None,
3767    dialect=None,
3768    **opts,
3769):
3770    if _is_wrong_expression(expression, into):
3771        expression = into(this=expression)
3772    instance = _maybe_copy(instance, copy)
3773    expression = maybe_parse(
3774        sql_or_expression=expression,
3775        prefix=prefix,
3776        into=into,
3777        dialect=dialect,
3778        **opts,
3779    )
3780    instance.set(arg, expression)
3781    return instance
3782
3783
3784def _apply_child_list_builder(
3785    *expressions,
3786    instance,
3787    arg,
3788    append=True,
3789    copy=True,
3790    prefix=None,
3791    into=None,
3792    dialect=None,
3793    properties=None,
3794    **opts,
3795):
3796    instance = _maybe_copy(instance, copy)
3797    parsed = []
3798    for expression in expressions:
3799        if _is_wrong_expression(expression, into):
3800            expression = into(expressions=[expression])
3801        expression = maybe_parse(
3802            expression,
3803            into=into,
3804            dialect=dialect,
3805            prefix=prefix,
3806            **opts,
3807        )
3808        parsed.extend(expression.expressions)
3809
3810    existing = instance.args.get(arg)
3811    if append and existing:
3812        parsed = existing.expressions + parsed
3813
3814    child = into(expressions=parsed)
3815    for k, v in (properties or {}).items():
3816        child.set(k, v)
3817    instance.set(arg, child)
3818    return instance
3819
3820
3821def _apply_list_builder(
3822    *expressions,
3823    instance,
3824    arg,
3825    append=True,
3826    copy=True,
3827    prefix=None,
3828    into=None,
3829    dialect=None,
3830    **opts,
3831):
3832    inst = _maybe_copy(instance, copy)
3833
3834    expressions = [
3835        maybe_parse(
3836            sql_or_expression=expression,
3837            into=into,
3838            prefix=prefix,
3839            dialect=dialect,
3840            **opts,
3841        )
3842        for expression in expressions
3843    ]
3844
3845    existing_expressions = inst.args.get(arg)
3846    if append and existing_expressions:
3847        expressions = existing_expressions + expressions
3848
3849    inst.set(arg, expressions)
3850    return inst
3851
3852
3853def _apply_conjunction_builder(
3854    *expressions,
3855    instance,
3856    arg,
3857    into=None,
3858    append=True,
3859    copy=True,
3860    dialect=None,
3861    **opts,
3862):
3863    expressions = [exp for exp in expressions if exp is not None and exp != ""]
3864    if not expressions:
3865        return instance
3866
3867    inst = _maybe_copy(instance, copy)
3868
3869    existing = inst.args.get(arg)
3870    if append and existing is not None:
3871        expressions = [existing.this if into else existing] + list(expressions)
3872
3873    node = and_(*expressions, dialect=dialect, **opts)
3874
3875    inst.set(arg, into(this=node) if into else node)
3876    return inst
3877
3878
3879def _combine(expressions, operator, dialect=None, **opts):
3880    expressions = [condition(expression, dialect=dialect, **opts) for expression in expressions]
3881    this = expressions[0]
3882    if expressions[1:]:
3883        this = _wrap_operator(this)
3884    for expression in expressions[1:]:
3885        this = operator(this=this, expression=_wrap_operator(expression))
3886    return this
3887
3888
3889def _wrap_operator(expression):
3890    if isinstance(expression, (And, Or, Not)):
3891        expression = Paren(this=expression)
3892    return expression
3893
3894
3895def union(left, right, distinct=True, dialect=None, **opts):
3896    """
3897    Initializes a syntax tree from one UNION expression.
3898
3899    Example:
3900        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
3901        'SELECT * FROM foo UNION SELECT * FROM bla'
3902
3903    Args:
3904        left (str | Expression): the SQL code string corresponding to the left-hand side.
3905            If an `Expression` instance is passed, it will be used as-is.
3906        right (str | Expression): the SQL code string corresponding to the right-hand side.
3907            If an `Expression` instance is passed, it will be used as-is.
3908        distinct (bool): set the DISTINCT flag if and only if this is true.
3909        dialect (str): the dialect used to parse the input expression.
3910        opts (kwargs): other options to use to parse the input expressions.
3911    Returns:
3912        Union: the syntax tree for the UNION expression.
3913    """
3914    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
3915    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
3916
3917    return Union(this=left, expression=right, distinct=distinct)
3918
3919
3920def intersect(left, right, distinct=True, dialect=None, **opts):
3921    """
3922    Initializes a syntax tree from one INTERSECT expression.
3923
3924    Example:
3925        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
3926        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
3927
3928    Args:
3929        left (str | Expression): the SQL code string corresponding to the left-hand side.
3930            If an `Expression` instance is passed, it will be used as-is.
3931        right (str | Expression): the SQL code string corresponding to the right-hand side.
3932            If an `Expression` instance is passed, it will be used as-is.
3933        distinct (bool): set the DISTINCT flag if and only if this is true.
3934        dialect (str): the dialect used to parse the input expression.
3935        opts (kwargs): other options to use to parse the input expressions.
3936    Returns:
3937        Intersect: the syntax tree for the INTERSECT expression.
3938    """
3939    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
3940    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
3941
3942    return Intersect(this=left, expression=right, distinct=distinct)
3943
3944
3945def except_(left, right, distinct=True, dialect=None, **opts):
3946    """
3947    Initializes a syntax tree from one EXCEPT expression.
3948
3949    Example:
3950        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
3951        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
3952
3953    Args:
3954        left (str | Expression): the SQL code string corresponding to the left-hand side.
3955            If an `Expression` instance is passed, it will be used as-is.
3956        right (str | Expression): the SQL code string corresponding to the right-hand side.
3957            If an `Expression` instance is passed, it will be used as-is.
3958        distinct (bool): set the DISTINCT flag if and only if this is true.
3959        dialect (str): the dialect used to parse the input expression.
3960        opts (kwargs): other options to use to parse the input expressions.
3961    Returns:
3962        Except: the syntax tree for the EXCEPT statement.
3963    """
3964    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
3965    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
3966
3967    return Except(this=left, expression=right, distinct=distinct)
3968
3969
3970def select(*expressions: str | Expression, dialect: DialectType = None, **opts) -> Select:
3971    """
3972    Initializes a syntax tree from one or multiple SELECT expressions.
3973
3974    Example:
3975        >>> select("col1", "col2").from_("tbl").sql()
3976        'SELECT col1, col2 FROM tbl'
3977
3978    Args:
3979        *expressions: the SQL code string to parse as the expressions of a
3980            SELECT statement. If an Expression instance is passed, this is used as-is.
3981        dialect: the dialect used to parse the input expressions (in the case that an
3982            input expression is a SQL string).
3983        **opts: other options to use to parse the input expressions (again, in the case
3984            that an input expression is a SQL string).
3985
3986    Returns:
3987        Select: the syntax tree for the SELECT statement.
3988    """
3989    return Select().select(*expressions, dialect=dialect, **opts)
3990
3991
3992def from_(*expressions, dialect=None, **opts) -> Select:
3993    """
3994    Initializes a syntax tree from a FROM expression.
3995
3996    Example:
3997        >>> from_("tbl").select("col1", "col2").sql()
3998        'SELECT col1, col2 FROM tbl'
3999
4000    Args:
4001        *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a
4002            SELECT statement. If an Expression instance is passed, this is used as-is.
4003        dialect (str): the dialect used to parse the input expression (in the case that the
4004            input expression is a SQL string).
4005        **opts: other options to use to parse the input expressions (again, in the case
4006            that the input expression is a SQL string).
4007
4008    Returns:
4009        Select: the syntax tree for the SELECT statement.
4010    """
4011    return Select().from_(*expressions, dialect=dialect, **opts)
4012
4013
4014def update(table, properties, where=None, from_=None, dialect=None, **opts) -> Update:
4015    """
4016    Creates an update statement.
4017
4018    Example:
4019        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
4020        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
4021
4022    Args:
4023        *properties (Dict[str, Any]): dictionary of properties to set which are
4024            auto converted to sql objects eg None -> NULL
4025        where (str): sql conditional parsed into a WHERE statement
4026        from_ (str): sql statement parsed into a FROM statement
4027        dialect (str): the dialect used to parse the input expressions.
4028        **opts: other options to use to parse the input expressions.
4029
4030    Returns:
4031        Update: the syntax tree for the UPDATE statement.
4032    """
4033    update = Update(this=maybe_parse(table, into=Table, dialect=dialect))
4034    update.set(
4035        "expressions",
4036        [
4037            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
4038            for k, v in properties.items()
4039        ],
4040    )
4041    if from_:
4042        update.set(
4043            "from",
4044            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
4045        )
4046    if isinstance(where, Condition):
4047        where = Where(this=where)
4048    if where:
4049        update.set(
4050            "where",
4051            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4052        )
4053    return update
4054
4055
4056def delete(table, where=None, dialect=None, **opts) -> Delete:
4057    """
4058    Builds a delete statement.
4059
4060    Example:
4061        >>> delete("my_table", where="id > 1").sql()
4062        'DELETE FROM my_table WHERE id > 1'
4063
4064    Args:
4065        where (str|Condition): sql conditional parsed into a WHERE statement
4066        dialect (str): the dialect used to parse the input expressions.
4067        **opts: other options to use to parse the input expressions.
4068
4069    Returns:
4070        Delete: the syntax tree for the DELETE statement.
4071    """
4072    return Delete(
4073        this=maybe_parse(table, into=Table, dialect=dialect, **opts),
4074        where=Where(this=where)
4075        if isinstance(where, Condition)
4076        else maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4077    )
4078
4079
4080def condition(expression, dialect=None, **opts) -> Condition:
4081    """
4082    Initialize a logical condition expression.
4083
4084    Example:
4085        >>> condition("x=1").sql()
4086        'x = 1'
4087
4088        This is helpful for composing larger logical syntax trees:
4089        >>> where = condition("x=1")
4090        >>> where = where.and_("y=1")
4091        >>> Select().from_("tbl").select("*").where(where).sql()
4092        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
4093
4094    Args:
4095        *expression (str | Expression): the SQL code string to parse.
4096            If an Expression instance is passed, this is used as-is.
4097        dialect (str): the dialect used to parse the input expression (in the case that the
4098            input expression is a SQL string).
4099        **opts: other options to use to parse the input expressions (again, in the case
4100            that the input expression is a SQL string).
4101
4102    Returns:
4103        Condition: the expression
4104    """
4105    return maybe_parse(  # type: ignore
4106        expression,
4107        into=Condition,
4108        dialect=dialect,
4109        **opts,
4110    )
4111
4112
4113def and_(*expressions, dialect=None, **opts) -> And:
4114    """
4115    Combine multiple conditions with an AND logical operator.
4116
4117    Example:
4118        >>> and_("x=1", and_("y=1", "z=1")).sql()
4119        'x = 1 AND (y = 1 AND z = 1)'
4120
4121    Args:
4122        *expressions (str | Expression): the SQL code strings to parse.
4123            If an Expression instance is passed, this is used as-is.
4124        dialect (str): the dialect used to parse the input expression.
4125        **opts: other options to use to parse the input expressions.
4126
4127    Returns:
4128        And: the new condition
4129    """
4130    return _combine(expressions, And, dialect, **opts)
4131
4132
4133def or_(*expressions, dialect=None, **opts) -> Or:
4134    """
4135    Combine multiple conditions with an OR logical operator.
4136
4137    Example:
4138        >>> or_("x=1", or_("y=1", "z=1")).sql()
4139        'x = 1 OR (y = 1 OR z = 1)'
4140
4141    Args:
4142        *expressions (str | Expression): the SQL code strings to parse.
4143            If an Expression instance is passed, this is used as-is.
4144        dialect (str): the dialect used to parse the input expression.
4145        **opts: other options to use to parse the input expressions.
4146
4147    Returns:
4148        Or: the new condition
4149    """
4150    return _combine(expressions, Or, dialect, **opts)
4151
4152
4153def not_(expression, dialect=None, **opts) -> Not:
4154    """
4155    Wrap a condition with a NOT operator.
4156
4157    Example:
4158        >>> not_("this_suit='black'").sql()
4159        "NOT this_suit = 'black'"
4160
4161    Args:
4162        expression (str | Expression): the SQL code strings to parse.
4163            If an Expression instance is passed, this is used as-is.
4164        dialect (str): the dialect used to parse the input expression.
4165        **opts: other options to use to parse the input expressions.
4166
4167    Returns:
4168        Not: the new condition
4169    """
4170    this = condition(
4171        expression,
4172        dialect=dialect,
4173        **opts,
4174    )
4175    return Not(this=_wrap_operator(this))
4176
4177
4178def paren(expression) -> Paren:
4179    return Paren(this=expression)
4180
4181
4182SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
4183
4184
4185@t.overload
4186def to_identifier(name: None, quoted: t.Optional[bool] = None) -> None:
4187    ...
4188
4189
4190@t.overload
4191def to_identifier(name: str | Identifier, quoted: t.Optional[bool] = None) -> Identifier:
4192    ...
4193
4194
4195def to_identifier(name, quoted=None):
4196    """Builds an identifier.
4197
4198    Args:
4199        name: The name to turn into an identifier.
4200        quoted: Whether or not force quote the identifier.
4201
4202    Returns:
4203        The identifier ast node.
4204    """
4205
4206    if name is None:
4207        return None
4208
4209    if isinstance(name, Identifier):
4210        identifier = name
4211    elif isinstance(name, str):
4212        identifier = Identifier(
4213            this=name,
4214            quoted=not re.match(SAFE_IDENTIFIER_RE, name) if quoted is None else quoted,
4215        )
4216    else:
4217        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
4218    return identifier
4219
4220
4221INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
4222
4223
4224def to_interval(interval: str | Literal) -> Interval:
4225    """Builds an interval expression from a string like '1 day' or '5 months'."""
4226    if isinstance(interval, Literal):
4227        if not interval.is_string:
4228            raise ValueError("Invalid interval string.")
4229
4230        interval = interval.this
4231
4232    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
4233
4234    if not interval_parts:
4235        raise ValueError("Invalid interval string.")
4236
4237    return Interval(
4238        this=Literal.string(interval_parts.group(1)),
4239        unit=Var(this=interval_parts.group(2)),
4240    )
4241
4242
4243@t.overload
4244def to_table(sql_path: str | Table, **kwargs) -> Table:
4245    ...
4246
4247
4248@t.overload
4249def to_table(sql_path: None, **kwargs) -> None:
4250    ...
4251
4252
4253def to_table(sql_path: t.Optional[str | Table], **kwargs) -> t.Optional[Table]:
4254    """
4255    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
4256    If a table is passed in then that table is returned.
4257
4258    Args:
4259        sql_path: a `[catalog].[schema].[table]` string.
4260
4261    Returns:
4262        A table expression.
4263    """
4264    if sql_path is None or isinstance(sql_path, Table):
4265        return sql_path
4266    if not isinstance(sql_path, str):
4267        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
4268
4269    catalog, db, table_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 3))
4270    return Table(this=table_name, db=db, catalog=catalog, **kwargs)
4271
4272
4273def to_column(sql_path: str | Column, **kwargs) -> Column:
4274    """
4275    Create a column from a `[table].[column]` sql path. Schema is optional.
4276
4277    If a column is passed in then that column is returned.
4278
4279    Args:
4280        sql_path: `[table].[column]` string
4281    Returns:
4282        Table: A column expression
4283    """
4284    if sql_path is None or isinstance(sql_path, Column):
4285        return sql_path
4286    if not isinstance(sql_path, str):
4287        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
4288    table_name, column_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 2))
4289    return Column(this=column_name, table=table_name, **kwargs)
4290
4291
4292def alias_(
4293    expression: str | Expression,
4294    alias: str | Identifier,
4295    table: bool | t.Sequence[str | Identifier] = False,
4296    quoted: t.Optional[bool] = None,
4297    dialect: DialectType = None,
4298    **opts,
4299):
4300    """Create an Alias expression.
4301
4302    Example:
4303        >>> alias_('foo', 'bar').sql()
4304        'foo AS bar'
4305
4306        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
4307        '(SELECT 1, 2) AS bar(a, b)'
4308
4309    Args:
4310        expression: the SQL code strings to parse.
4311            If an Expression instance is passed, this is used as-is.
4312        alias: the alias name to use. If the name has
4313            special characters it is quoted.
4314        table: Whether or not to create a table alias, can also be a list of columns.
4315        quoted: whether or not to quote the alias
4316        dialect: the dialect used to parse the input expression.
4317        **opts: other options to use to parse the input expressions.
4318
4319    Returns:
4320        Alias: the aliased expression
4321    """
4322    exp = maybe_parse(expression, dialect=dialect, **opts)
4323    alias = to_identifier(alias, quoted=quoted)
4324
4325    if table:
4326        table_alias = TableAlias(this=alias)
4327        exp.set("alias", table_alias)
4328
4329        if not isinstance(table, bool):
4330            for column in table:
4331                table_alias.append("columns", to_identifier(column, quoted=quoted))
4332
4333        return exp
4334
4335    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
4336    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
4337    # for the complete Window expression.
4338    #
4339    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
4340
4341    if "alias" in exp.arg_types and not isinstance(exp, Window):
4342        exp = exp.copy()
4343        exp.set("alias", alias)
4344        return exp
4345    return Alias(this=exp, alias=alias)
4346
4347
4348def subquery(expression, alias=None, dialect=None, **opts):
4349    """
4350    Build a subquery expression.
4351
4352    Example:
4353        >>> subquery('select x from tbl', 'bar').select('x').sql()
4354        'SELECT x FROM (SELECT x FROM tbl) AS bar'
4355
4356    Args:
4357        expression (str | Expression): the SQL code strings to parse.
4358            If an Expression instance is passed, this is used as-is.
4359        alias (str | Expression): the alias name to use.
4360        dialect (str): the dialect used to parse the input expression.
4361        **opts: other options to use to parse the input expressions.
4362
4363    Returns:
4364        Select: a new select with the subquery expression included
4365    """
4366
4367    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
4368    return Select().from_(expression, dialect=dialect, **opts)
4369
4370
4371def column(
4372    col: str | Identifier,
4373    table: t.Optional[str | Identifier] = None,
4374    schema: t.Optional[str | Identifier] = None,
4375    quoted: t.Optional[bool] = None,
4376) -> Column:
4377    """
4378    Build a Column.
4379
4380    Args:
4381        col: column name
4382        table: table name
4383        schema: schema name
4384        quoted: whether or not to force quote each part
4385    Returns:
4386        Column: column instance
4387    """
4388    return Column(
4389        this=to_identifier(col, quoted=quoted),
4390        table=to_identifier(table, quoted=quoted),
4391        schema=to_identifier(schema, quoted=quoted),
4392    )
4393
4394
4395def cast(expression: str | Expression, to: str | DataType | DataType.Type, **opts) -> Cast:
4396    """Cast an expression to a data type.
4397
4398    Example:
4399        >>> cast('x + 1', 'int').sql()
4400        'CAST(x + 1 AS INT)'
4401
4402    Args:
4403        expression: The expression to cast.
4404        to: The datatype to cast to.
4405
4406    Returns:
4407        A cast node.
4408    """
4409    expression = maybe_parse(expression, **opts)
4410    return Cast(this=expression, to=DataType.build(to, **opts))
4411
4412
4413def table_(table, db=None, catalog=None, quoted=None, alias=None) -> Table:
4414    """Build a Table.
4415
4416    Args:
4417        table (str | Expression): column name
4418        db (str | Expression): db name
4419        catalog (str | Expression): catalog name
4420
4421    Returns:
4422        Table: table instance
4423    """
4424    return Table(
4425        this=to_identifier(table, quoted=quoted),
4426        db=to_identifier(db, quoted=quoted),
4427        catalog=to_identifier(catalog, quoted=quoted),
4428        alias=TableAlias(this=to_identifier(alias)) if alias else None,
4429    )
4430
4431
4432def values(
4433    values: t.Iterable[t.Tuple[t.Any, ...]],
4434    alias: t.Optional[str] = None,
4435    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
4436) -> Values:
4437    """Build VALUES statement.
4438
4439    Example:
4440        >>> values([(1, '2')]).sql()
4441        "VALUES (1, '2')"
4442
4443    Args:
4444        values: values statements that will be converted to SQL
4445        alias: optional alias
4446        columns: Optional list of ordered column names or ordered dictionary of column names to types.
4447         If either are provided then an alias is also required.
4448         If a dictionary is provided then the first column of the values will be casted to the expected type
4449         in order to help with type inference.
4450
4451    Returns:
4452        Values: the Values expression object
4453    """
4454    if columns and not alias:
4455        raise ValueError("Alias is required when providing columns")
4456    table_alias = (
4457        TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
4458        if columns
4459        else TableAlias(this=to_identifier(alias) if alias else None)
4460    )
4461    expressions = [convert(tup) for tup in values]
4462    if columns and isinstance(columns, dict):
4463        types = list(columns.values())
4464        expressions[0].set(
4465            "expressions",
4466            [cast(x, types[i]) for i, x in enumerate(expressions[0].expressions)],
4467        )
4468    return Values(
4469        expressions=expressions,
4470        alias=table_alias,
4471    )
4472
4473
4474def var(name: t.Optional[str | Expression]) -> Var:
4475    """Build a SQL variable.
4476
4477    Example:
4478        >>> repr(var('x'))
4479        '(VAR this: x)'
4480
4481        >>> repr(var(column('x', table='y')))
4482        '(VAR this: x)'
4483
4484    Args:
4485        name: The name of the var or an expression who's name will become the var.
4486
4487    Returns:
4488        The new variable node.
4489    """
4490    if not name:
4491        raise ValueError(f"Cannot convert empty name into var.")
4492
4493    if isinstance(name, Expression):
4494        name = name.name
4495    return Var(this=name)
4496
4497
4498def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
4499    """Build ALTER TABLE... RENAME... expression
4500
4501    Args:
4502        old_name: The old name of the table
4503        new_name: The new name of the table
4504
4505    Returns:
4506        Alter table expression
4507    """
4508    old_table = to_table(old_name)
4509    new_table = to_table(new_name)
4510    return AlterTable(
4511        this=old_table,
4512        actions=[
4513            RenameTable(this=new_table),
4514        ],
4515    )
4516
4517
4518def convert(value) -> Expression:
4519    """Convert a python value into an expression object.
4520
4521    Raises an error if a conversion is not possible.
4522
4523    Args:
4524        value (Any): a python object
4525
4526    Returns:
4527        Expression: the equivalent expression object
4528    """
4529    if isinstance(value, Expression):
4530        return value
4531    if value is None:
4532        return NULL
4533    if isinstance(value, bool):
4534        return Boolean(this=value)
4535    if isinstance(value, str):
4536        return Literal.string(value)
4537    if isinstance(value, float) and math.isnan(value):
4538        return NULL
4539    if isinstance(value, numbers.Number):
4540        return Literal.number(value)
4541    if isinstance(value, tuple):
4542        return Tuple(expressions=[convert(v) for v in value])
4543    if isinstance(value, list):
4544        return Array(expressions=[convert(v) for v in value])
4545    if isinstance(value, dict):
4546        return Map(
4547            keys=[convert(k) for k in value],
4548            values=[convert(v) for v in value.values()],
4549        )
4550    if isinstance(value, datetime.datetime):
4551        datetime_literal = Literal.string(
4552            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
4553        )
4554        return TimeStrToTime(this=datetime_literal)
4555    if isinstance(value, datetime.date):
4556        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
4557        return DateStrToDate(this=date_literal)
4558    raise ValueError(f"Cannot convert {value}")
4559
4560
4561def replace_children(expression, fun):
4562    """
4563    Replace children of an expression with the result of a lambda fun(child) -> exp.
4564    """
4565    for k, v in expression.args.items():
4566        is_list_arg = isinstance(v, list)
4567
4568        child_nodes = v if is_list_arg else [v]
4569        new_child_nodes = []
4570
4571        for cn in child_nodes:
4572            if isinstance(cn, Expression):
4573                for child_node in ensure_collection(fun(cn)):
4574                    new_child_nodes.append(child_node)
4575                    child_node.parent = expression
4576                    child_node.arg_key = k
4577            else:
4578                new_child_nodes.append(cn)
4579
4580        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
4581
4582
4583def column_table_names(expression):
4584    """
4585    Return all table names referenced through columns in an expression.
4586
4587    Example:
4588        >>> import sqlglot
4589        >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e"))
4590        ['c', 'a']
4591
4592    Args:
4593        expression (sqlglot.Expression): expression to find table names
4594
4595    Returns:
4596        list: A list of unique names
4597    """
4598    return list(dict.fromkeys(column.table for column in expression.find_all(Column)))
4599
4600
4601def table_name(table) -> str:
4602    """Get the full name of a table as a string.
4603
4604    Args:
4605        table (exp.Table | str): table expression node or string.
4606
4607    Examples:
4608        >>> from sqlglot import exp, parse_one
4609        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
4610        'a.b.c'
4611
4612    Returns:
4613        The table name.
4614    """
4615
4616    table = maybe_parse(table, into=Table)
4617
4618    if not table:
4619        raise ValueError(f"Cannot parse {table}")
4620
4621    return ".".join(
4622        part
4623        for part in (
4624            table.text("catalog"),
4625            table.text("db"),
4626            table.name,
4627        )
4628        if part
4629    )
4630
4631
4632def replace_tables(expression, mapping):
4633    """Replace all tables in expression according to the mapping.
4634
4635    Args:
4636        expression (sqlglot.Expression): expression node to be transformed and replaced.
4637        mapping (Dict[str, str]): mapping of table names.
4638
4639    Examples:
4640        >>> from sqlglot import exp, parse_one
4641        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
4642        'SELECT * FROM c'
4643
4644    Returns:
4645        The mapped expression.
4646    """
4647
4648    def _replace_tables(node):
4649        if isinstance(node, Table):
4650            new_name = mapping.get(table_name(node))
4651            if new_name:
4652                return to_table(
4653                    new_name,
4654                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
4655                )
4656        return node
4657
4658    return expression.transform(_replace_tables)
4659
4660
4661def replace_placeholders(expression, *args, **kwargs):
4662    """Replace placeholders in an expression.
4663
4664    Args:
4665        expression (sqlglot.Expression): expression node to be transformed and replaced.
4666        args: positional names that will substitute unnamed placeholders in the given order.
4667        kwargs: keyword arguments that will substitute named placeholders.
4668
4669    Examples:
4670        >>> from sqlglot import exp, parse_one
4671        >>> replace_placeholders(
4672        ...     parse_one("select * from :tbl where ? = ?"), "a", "b", tbl="foo"
4673        ... ).sql()
4674        'SELECT * FROM foo WHERE a = b'
4675
4676    Returns:
4677        The mapped expression.
4678    """
4679
4680    def _replace_placeholders(node, args, **kwargs):
4681        if isinstance(node, Placeholder):
4682            if node.name:
4683                new_name = kwargs.get(node.name)
4684                if new_name:
4685                    return to_identifier(new_name)
4686            else:
4687                try:
4688                    return to_identifier(next(args))
4689                except StopIteration:
4690                    pass
4691        return node
4692
4693    return expression.transform(_replace_placeholders, iter(args), **kwargs)
4694
4695
4696def expand(expression: Expression, sources: t.Dict[str, Subqueryable], copy=True) -> Expression:
4697    """Transforms an expression by expanding all referenced sources into subqueries.
4698
4699    Examples:
4700        >>> from sqlglot import parse_one
4701        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
4702        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
4703
4704    Args:
4705        expression: The expression to expand.
4706        sources: A dictionary of name to Subqueryables.
4707        copy: Whether or not to copy the expression during transformation. Defaults to True.
4708
4709    Returns:
4710        The transformed expression.
4711    """
4712
4713    def _expand(node: Expression):
4714        if isinstance(node, Table):
4715            name = table_name(node)
4716            source = sources.get(name)
4717            if source:
4718                subquery = source.subquery(node.alias or name)
4719                subquery.comments = [f"source: {name}"]
4720                return subquery
4721        return node
4722
4723    return expression.transform(_expand, copy=copy)
4724
4725
4726def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
4727    """
4728    Returns a Func expression.
4729
4730    Examples:
4731        >>> func("abs", 5).sql()
4732        'ABS(5)'
4733
4734        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
4735        'CAST(5 AS DOUBLE)'
4736
4737    Args:
4738        name: the name of the function to build.
4739        args: the args used to instantiate the function of interest.
4740        dialect: the source dialect.
4741        kwargs: the kwargs used to instantiate the function of interest.
4742
4743    Note:
4744        The arguments `args` and `kwargs` are mutually exclusive.
4745
4746    Returns:
4747        An instance of the function of interest, or an anonymous function, if `name` doesn't
4748        correspond to an existing `sqlglot.expressions.Func` class.
4749    """
4750    if args and kwargs:
4751        raise ValueError("Can't use both args and kwargs to instantiate a function.")
4752
4753    from sqlglot.dialects.dialect import Dialect
4754
4755    args = tuple(convert(arg) for arg in args)
4756    kwargs = {key: convert(value) for key, value in kwargs.items()}
4757
4758    parser = Dialect.get_or_raise(dialect)().parser()
4759    from_args_list = parser.FUNCTIONS.get(name.upper())
4760
4761    if from_args_list:
4762        function = from_args_list(args) if args else from_args_list.__self__(**kwargs)  # type: ignore
4763    else:
4764        kwargs = kwargs or {"expressions": args}
4765        function = Anonymous(this=name, **kwargs)
4766
4767    for error_message in function.error_messages(args):
4768        raise ValueError(error_message)
4769
4770    return function
4771
4772
4773def true():
4774    """
4775    Returns a true Boolean expression.
4776    """
4777    return Boolean(this=True)
4778
4779
4780def false():
4781    """
4782    Returns a false Boolean expression.
4783    """
4784    return Boolean(this=False)
4785
4786
4787def null():
4788    """
4789    Returns a Null expression.
4790    """
4791    return Null()
4792
4793
4794# TODO: deprecate this
4795TRUE = Boolean(this=True)
4796FALSE = Boolean(this=False)
4797NULL = Null()